How return a count(*) of 0 instead of NULL

Question

I have this bit of code:

SELECT Project, Financial_Year, COUNT(*) AS HighRiskCount
INTO #HighRisk 
FROM #TempRisk1
WHERE Risk_1 = 3
GROUP BY Project, Financial_Year

where it's not returning any rows when the count is zero. How do I make these rows appear with the HighRiskCount set as 0?

Solution 1

You can't select the values from the table when the row count is 0. Where would it get the values for the nonexistent rows?

To do this, you'll have to have another table that defines your list of valid Project and Financial_Year values. You'll then select from this table, perform a left join on your existing table, then do the grouping.

Something like this:

SELECT l.Project, l.Financial_Year, COUNT(t.Project) AS HighRiskCount
INTO #HighRisk 
FROM MasterRiskList l
left join #TempRisk1 t on t.Project = l.Project and t.Financial_Year = l.Financial_Year
WHERE t.Risk_1 = 3
GROUP BY l.Project, l.Financial_Year

Solution 2

Wrap your SELECT Query in an ISNULL:

SELECT ISNULL((SELECT Project, Financial_Year, COUNT(*) AS hrc
INTO #HighRisk 
FROM #TempRisk1
WHERE Risk_1 = 3
GROUP BY Project, Financial_Year),0) AS HighRiskCount

If your SELECT returns a number, it will pass through. If it returns NULL, the 0 will pass through.

Solution 3

Assuming you have your 'Project' and 'Financial_Year' where Risk_1 is different than 3, and those are the ones you intend to include.

SELECT Project, Financial_Year, SUM(CASE WHEN RISK_1 = 3 THEN 1 ELSE 0 END) AS HighRiskCount
INTO #HighRisk 
FROM #TempRisk1
GROUP BY Project, Financial_Year

Notice i removed the where part.

By the way, your current query is not returning null, it is returning no rows.

Solution 4

Use:

   SELECT x.Project, x.financial_Year, 
          COUNT(y.*) AS HighRiskCount
     INTO #HighRisk 
     FROM (SELECT DISTINCT t.project, t.financial_year
             FROM #TempRisk1
            WHERE t.Risk_1 = 3) x
LEFT JOIN #TempRisk1 y ON y.project = x.project
                      AND y.financial_year = x.financial_year
 GROUP BY x.Project, x.Financial_Year

The only way to get zero counts is to use an OUTER join against a list of the distinct values you want to see zero counts for.

View more solutions

Comments

[Adam Robinson]

As with the other similar answer, this won't work. Where is it going to get values for Project and Financial_Year if the rows aren't there to provide them?

[Jamie F]

Key point. All the other answers so far miss this. What values would be placed into Project and Financial_Year for the rows when the count is zero?

[Jeremy Wiggins]

You're right, I realized this about 3 seconds after I posted. It's times like this I wish there was a way to delete on SO, but I suppose next time I'll take the additional 3 seconds to think before submitting... upvote coming to ya...

[deutschZuid]

It's selecting the project and financial year from the master list, so it will display those when the count is zero I guess.

[Adam Robinson]

@JamesJiao: The MasterRiskList is something I added to my query as an example; it's not in the OP's query.

[deutschZuid]

I know.. what's what I referred to.

[The Lizard]

This is what I was looking for. Works like charm.