I have a table that calls Ordem , it has a field that represents the Status of the service order (0 to 7), I need to plot a graph in Ireport, but I wanted to display in the caption the name of the status when the value is zero.
The select would have to return something of the type:
Status - Total
0 30
1 2
2 0
3 9
4 0
5 44
6 6
7 0
Does anyone have any examples of Query in Sql Server that can return all 8 statuses?
You can "simulate" a table with all the existing statuses and then make a select on this table by linking it through a left join with the Ordem table, like this:
declare @Status Table (
Status int)
insert into @Status values (0), (1), (2), (3), (4), (5), (6), (7)
select
Status.Status,
count(Ordem.Satus) as Total
from
@Status as Status
left join Ordem on Status.Status = Ordem.Satus
group by
Status.Status
order by
Status.Status
In the code above, I used the SQL Server feature called table variable , which allows you to declare a variable as being of a table type, so you handle this variable as if it were dealing with a normal table.
Some notes:
You need to give alias to the table variable to use its fields in SELECT. I made it this way: from @Status Status (note the use of keyword as ).
I made the select on table variable by linking it with left join to ensure that all statuses are returned regardless of whether there is value for them in table Ordem .
If I could manipulate the query result in the application code, I would do a single simple query (without using table variable ) and loop the application from 0 to 7, assigning Zero as total for the nonexistent statuses in the Ordem table.
There is a very interesting and generic solution to these cases where you want to display values that do not exist in the database. Values where JOINS do not resolve, since there are no records in the database.
This solution is based on the use of a table that contains only one field of type int and is populated with 999999 of records.
It is the famous Numbers table ( link ). Obviously, the name does not matter, but it is called by many of Numbers.
How to create and populate this table in SQL Server can be seen here: link
To use it, in its context, do so:
SELECT NUMBER, COUNT(T.STATUS)
FROM NUMBERS N
LEFT JOIN TABELA T
ON (T.STATUS = N.NUMBER)
WHERE N.NUMBER <= 8
GROUP BY NUMBER
SQLFiddle with this example: link
Understand Numbers like a Swiss Army Knife. You can solve various problems of this type using it.
Another well-known example is when we need to show production for every day of the month, even for the days where the production was 0. With Numbers this is very easy, since it contains every day.