Show zero in group by SQL Server

Question

I have a table that calls Ordem, on it has a field that represents the status of the service order (0 to 7), I need to plot a graph in Ireport, only I wanted to display in the legend the status name when the value is zero.

select would have to return something like:

Status - Total
0        30
1        2
2        0
3        9
4        0
5        44
6        6
7        0

Does anyone have an example of Query in Sql Server that can return 8 status ?

Answer 1

There is a very interesting and generic solution for these cases, where you want to display values that do not exist in the database. Values where JOINS do not solve, since there are no records in the database.

This solution is based on the use of a table that contains only one int field and is filled with 999999 records.

It’s the famous Numbers table (https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable). Obviously, the name does not matter, but is called by many Numbers.

How to create and fill this table in SQL Server can be seen here: /a/80506/3084

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: http://sqlfiddle.com/#! 3/4b80e/1/0

Understand Numbers as a Swiss Army knife. You can solve a lot of these problems using it.

Another very famous example is when we need to show the production for every day of the month, including for the days where the production was 0. With Numbers this is very easy, since it contains every day.

Answer 2

You can "simulate" a table with all existing status and then make a select on this table by linking it through a left Join with the table Ordem, thus:

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 above code, I used the SQL Server feature called variable table, which allows you to declare a variable as being of a table type, so you deal with this variable as if you were dealing with a normal table.

Some notes:

• You need to give a alias to the variable table to use your fields in SELECT. I did so: from @Status as Status (note the keyword usage as).

• I did select on variable table linking her with left Join in order to ensure that all status is returned regardless of whether there is value for them in the table Ordem.

Other option:

If I could manipulate the result of the query in the application code, I would do a single simple query (without using variable table) and would loop from 0 to 7 in the app, assigning Zero as the total for the non-existent status in the table Ordem.