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?

Answer 1

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.

Answer 2

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.