How can I show category name and total number

Question

If there is no matching row for status 1, but I still want to show the Category Name and Total number as 0. How should I modify the below SQL? Thank you!

SELECT 
     CASE WHEN STATUS=1 THEN 'CATEGORY A' else 'CATEGORY B' end as [Category Name],
     COUNT(*) as [Total Number in Each Category]
FROM TABLE
WHERE STATUS IN (1,2)

You could use a [pivot](http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) with hard coded categories and a count of matching rows for each one. — CactusCake, Apr 21 '16 at 16:40
The only other way I would suggest is creating a table (or temp table) with all the possible categories, and then left joining your data to it and using `ifnull(count(joined_table.*),0)` to show your aggregated results per category. This is often how people display results by month when some months might have zero matching records. See [this question](http://stackoverflow.com/questions/21789777/counting-records-by-year-and-month-including-zero-counts) for example. — CactusCake, Apr 21 '16 at 17:38

score 1 · Answer 1 · answered Apr 21 '16 at 21:37

Union in the blank values with the original query in a CTE (or temp table if you prefer) then query the sum from the CTE:

with cte as (SELECT case WHEN STATUS=1 THEN 'CATEGORY A' else 'CATEGORY B' end as [Category Name]
                    ,COUNT(*) as [Total Number in Each Category]
            FROM TABLE
            WHERE STATUS IN (1,2)
            UNION ALL
            select  'CATEGORY A' as [Category Name]
                    ,0 as [Total Number in Each Category]
            UNION ALL
            select  'CATEGORY B' as [Category Name]
                    ,0 as [Total Number in Each Category])

select [Category Name]
        , sum([Total Number in Each Category]) as [Total Number in Each Category]
from CTE
group by [Category Name]

And I might clarify that if you had changing categories or many categories, you could union a select distinct category, 0 as total instead of hardcoding the list. — Cyndi Baker, Apr 25 '16 at 19:04

How can I show category name and total number

1 Answers1