group by multiple columns having count 0 for all

Question

I have a query where it has to return count of 0, if no records found on group by. but i am getting empty results.

SELECT DATEPART(YEAR, s.CreatedAt) AS 'Year',DATEPART(MONTH, s.CreatedAt) AS 
'Month', count(*) AS 'count'
FROM table1 t1
JOIN table2 t2 on t1.id = t2.id
WHERE t1.Id IN (
SELECT id FROM table3 WHERE Id IN (
SELECT id FROM table4 WHERE Gradingresult LIKE '%abcd%'
GROUP BY id
)) AND t1.column1 LIKE 'hello' AND t1.column2='world' AND t2.column1 != 
'somestring' 
AND t1.CreatedAt BETWEEN  DATEADD(YEAR, -1, GETDATE()) AND GETDATE() 
GROUP BY DATEPART(YEAR, t1.CreatedAt), DATEPART(MONTH, t1.CreatedAt)

i want it to be displayed like below,

    year month      count
    2019    2         0
    2019    4         0
    2018    7         0

but i am getting result as below

    year   month    count

nothing here, no rows.

i have tried different ways like case statement, isnull(), left join and others but not able to get desired result

editing i have considered year as well updated actual query i have, if i don't included t2.column1 condition i am getting below results

    year  month   count
    2019  5        10
    2019  4        25
    2019  2         9
    2018  10       19

if i include above specified condition i should get desired result

Have a help table (or recursive cte) returning 1 - 12. outer join. — jarlh, Sep 09 '19 at 12:12
Possible duplicate of [How to return empty groups in SQL GROUP BY clause](https://stackoverflow.com/questions/1980678/how-to-return-empty-groups-in-sql-group-by-clause) — zuku, Sep 09 '19 at 12:21

score 0 · Accepted Answer · answered Sep 09 '19 at 12:13

0

If there are no rows in the table, there are no rows in the result set. You can use a left join to get the rows you want:

select v.mon, count(t.createdat)
from (values (2), (4), (7)) v(mon) left join
     tableT t
     on month(t.createdat) = v.mon
group by v.mon
order by v.mon;

You need to be very careful with this formulation, because you have not included the year. When working with months, you should usually be considering the year as well.

answered Sep 09 '19 at 12:13

Gordon Linoff

1,242,037
58
646
786

i am considering year as well, but i didn't specify in question, but still getting empty result – Jyothi Prakash Sep 09 '19 at 12:17
@JyothiPrakash . . . This *cannot* return an empty result with a `left join`. – Gordon Linoff Sep 09 '19 at 12:31
thank you it worked after doing some modification based on your suggestion – Jyothi Prakash Sep 13 '19 at 05:45

group by multiple columns having count 0 for all

1 Answers1