CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functions
alamodey
alamodey

Reputation: 14938

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

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?

Upvotes: 15

Views: 93052

Answers (7)

Steve Mol
Steve Mol

Reputation: 153

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.

Upvotes: 8

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Little longer, but what about this as a solution?

IF EXISTS (
        SELECT *
        FROM #TempRisk1
        WHERE Risk_1 = 3
    )
    BEGIN
        SELECT Project, Financial_Year, COUNT(*) AS HighRiskCount
        INTO #HighRisk 
        FROM #TempRisk1
        WHERE Risk_1 = 3
        GROUP BY Project, Financial_Year
    END
ELSE
    BEGIN
        INSERT INTO #HighRisk 
            SELECT 'Project', 'Financial_Year', 0
    END

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

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.

Upvotes: 5

Larry Lustig
Larry Lustig

Reputation: 50970

SQL generally has a problem returning the values that aren't in a table. To accomplish this (without a stored procedure, in any event), you'll need another table that contains the missing values.

Assuming you want one row per project / financial year combination, you'll need a table that contains each valid Project, Finanical_Year combination:

 SELECT HR.Project, HR.Financial_Year, COUNT(HR.Risk_1) AS HighRiskCount
 INTO #HighRisk HR RIGHT OUTER JOIN ProjectYears PY
   ON HR.Project = PY.Project AND HR.Financial_Year = PY.Financial_Year
 FROM #TempRisk1
 WHERE Risk_1 = 3
 GROUP BY HR.Project, HR.Financial_Year

Note that we're taking advantage of the fact that COUNT() will only count non-NULL values to get a 0 COUNT result for those result set records that are made up only of data from the new ProjectYears table.

Alternatively, you might only one 0 count record to be returned per project (or maybe one per financial_year). You would modify the above solution so that the JOINed table has only that one column.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332531

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.

Upvotes: 4

Adam Robinson
Adam Robinson

Reputation: 185593

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

Upvotes: 13

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

MSDN - ISNULL function


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

Upvotes: -1

Related Questions