Ronald McDonald Ronald McDonald - 2 months ago 13
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.