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
.
You can put a modeling on Sqlfiddle?
– KaduAmaral
A simple consultation
select status, count(*) as total from tabela group by status
didn’t work for you? Look at this Sqlfiddle: http://sqlfiddle.com/#! 9/b72ea/2– Eduardo Silva
Then this query @Eduardo Silva I have already performed, but I need for example, when the status 7 is not inserted in the table return with the query the status 7 and the value 0. Only way I managed was using 8 select with UNION, but I think that way the query can be slow when there is a large amount of data.
– Pedro A.