how to fetch data from table based on column value and group by its name and count column value-CodePudding

This is body of the table:

ID  STATUS
1   Pending
2   Received
3   NULL
4   NULL

I have four categories of status- Pending, Received, Resolved & Rejected. At first any data is received from my website then this record shown as pending record. So, I want to count the status by their names, like -

STATUS    TOTAL
Pending    1
Received   1
Resolved   0
Rejected   0

The problem is that I face, in that table their is no Resolved and Rejected data. So how could I show the output as 0.

CodePudding user response：

You need a table of statuses from which you can outer join to your aggregated results:

with tot as (
    select status, Count(*) tot
    from t
    where status is not null
    group by status
)
select s.status, IsNull(t.tot,0) total
from (values ('Pending'),('Received'),('Resolved'),('Rejected'))s(status) 
left join tot t on t.status=s.status