Ronald McDonald Ronald McDonald - 26 days ago 9
SQL Question

SQL Do not return column if value is zero

This query returns one row with columns Ready, Processing, Complete, Failed and Error with totals for each. Is there a way to rewrite this query so that columns that have a total of zero are not returned?

I'm using this to populate the mschart control and I don't wan't labels on the chart if there are 0 instances of that category.

SELECT
SUM(CASE WHEN Status = 'R' THEN 1 ELSE 0 END) AS Ready,
SUM(CASE WHEN Status = 'P' THEN 1 ELSE 0 END) AS Processing,
SUM(CASE WHEN Status = 'C' THEN 1 ELSE 0 END) AS Complete,
SUM(CASE WHEN Status = 'F' THEN 1 ELSE 0 END) AS Failed,
SUM(CASE WHEN Status = 'E' THEN 1 ELSE 0 END) AS Error
FROM MailDefinition

Answer

What I would do is take what you have, throw it into an unpivot, then remove all of the 0 records.

select
    Type, 
    Sum
from
(
    SELECT                       
        SUM(CASE WHEN Status = 'R' THEN 1 ELSE 0 END) AS Ready,               
        SUM(CASE WHEN Status = 'P' THEN 1 ELSE 0 END) AS Processing,                
        SUM(CASE WHEN Status = 'C' THEN 1 ELSE 0 END) AS Complete,               
        SUM(CASE WHEN Status = 'F' THEN 1 ELSE 0 END) AS Failed,               
        SUM(CASE WHEN Status = 'E' THEN 1 ELSE 0 END) AS Error 
FROM  MailDefinition 
) a
unpivot
(
     Sum for Type in ([Ready],[Processing],[Complete],[Failed],[Error])
) u
where Sum>0

That does, of course, entail changing your chart some.

Comments