SQL Query with return a value when no records found-CodePudding

I am having problem with a query. If someone could help me of how to return a value of 0 when no records found.
Here's my SQL Statement. I tried to use the coalesce and max with this statement, received an error of

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT COUNT(Status) as Count,
       Status,
       [Area Name]
FROM  dbo.aspx_Inspection 
where [Area Name] like '%Frozen%'
  and (Status='Failed')
  AND DATEDIFF(day,[Assigned Date],GETDATE()) between 0 and 360
GROUP BY Status,
         [Area Name]
ORDER BY Status desc

Result

|(No column name)|  Status  |Area Name|
|----------------|----------|-----------|
|26               | Failed   |Frozen Pond Arena|

CodePudding user response：

Assuming you have a master list of status in a table like Master.Status, you could do this

SELECT ISNULL(T.Count,0), M.Status, T.AreaName
FROM
Master.Status M 
LEFT JOIN
(
SELECT COUNT(Status) as Count,
       Status,
       [Area Name]
FROM  dbo.aspx_Inspection 
where [Area Name] like '%Frozen%'
  and (Status='Failed')
  AND DATEDIFF(day,[Assigned Date],GETDATE()) between 0 and 360
GROUP BY Status,
         [Area Name]
)T
ON T.Status=M.Status
ORDER BY M.Status desc

In case Master.Status does not exists, and you are sure that all status codes are in dbo.aspx_Inspection you could also do

SELECT ISNULL(T.Count,0), M.Status, T.AreaName
FROM
(SELECT DISTINCT Status FROM  dbo.aspx_Inspection ) M 
LEFT JOIN
(
SELECT COUNT(Status) as Count,
       Status,
       [Area Name]
FROM  dbo.aspx_Inspection 
where [Area Name] like '%Frozen%'
  and (Status='Failed')
  AND DATEDIFF(day,[Assigned Date],GETDATE()) between 0 and 360
GROUP BY Status,
         [Area Name]
)T
ON T.Status=M.Status
ORDER BY M.Status desc