CODEWITHSUNDEEP
sqlsql-server
CoolArchTek
CoolArchTek

Reputation: 3849

SQL Server - Get a count even if there are no results corresponding table?

The query below returns problems created for a developer in last 12 months. The query below works fine, but I wanted this to return 0 when there is no match.

SELECT 
   count(1) as count,
   MONTH(pro.[DATE_CREATED]) AS MONTHNAME, 
   YEAR(pro.[DATE_CREATED]) AS YEARNAME ,
   problemStatus.TypeName
FROM    
   [TestDB].[dbo].PROBLEMS AS pro 
LEFT JOIN 
   [TestDB].[dbo].PROBLEMSTATUS AS problemStatus ON problemStatus.ID = pro.ID_STATUS 
LEFT JOIN 
   [TestDB].[dbo].CUSTOMER AS cust ON pro.ID = cust.ID 
WHERE 
   pro.ID = 1010101010
   AND pro.[DATE_CREATED] >= DATEADD(m, -6, current_timestamp) 
GROUP BY 
   MONTH(pro.[DATE_CREATED]), 
   YEAR(pro.[DATE_CREATED]), 
   problemStatus.type
ORDER BY 
   MONTH(pro.[DATE_CREATED]) DESC

Right now the query returns,

Count   Month   Year    Status
1   12  2013    Fixed
1   11  2013    Fixed
1   9   2013    Fixed
1   8   2013    Fixed
1   2   2014    Fixed
1   1   2014    Opened
1   1   2014    Fixed

I want this to return

Count   Month   Year    Status
1   12  2013    Fixed
0   12  2013    Opened
1   11  2013    Fixed
0   11  2013    Opened
1   9   2013    Fixed
0   9   2013    Opened
1   8   2013    Fixed
0   8   2013    Opened
1   2   2014    Fixed
0   2   2014    Opened
1   1   2014    Opened
1   1   2014    Fixed

Upvotes: 0

Views: 145

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you have data in the table for all months, but just not for that ID, then try this:

SELECT sum(case when pro.ID = 1010101010 then 1 else 0 end) as count,
       MONTH(pro.[DATE_CREATED]) AS MONTHNAME, 
       YEAR(pro.[DATE_CREATED]) AS YEARNAME ,
       coalesce(max(case when pro.ID = 1010101010 then problemStatus.TypeName end), 'Opened') as problemStatus
FROM    [TestDB].[dbo].PROBLEMS AS pro 
left JOIN [TestDB].[dbo].PROBLEMSTATUS AS problemStatus 
ON problemStatus.ID = pro.ID_STATUS 
left  JOIN [TestDB].[dbo].CUSTOMER AS cust 
ON  pro.ID = cust.ID 
WHERE pro.[DATE_CREATED] >= DATEADD(m, -6, current_timestamp) 
GROUP BY MONTH(pro.[DATE_CREATED]), YEAR(pro.[DATE_CREATED]) , problemStatus.type
ORDER BY MONTH(pro.[DATE_CREATED]) DESC;

Upvotes: 1

Related Questions