1

I need some help with the following query.

Select date, Source,count(*) as TOTALCOUNT
from Table A
where date = '2014-10-15' and Source = 'EMAIL'
group by date,source

There is no EMAIL source for a particular day. So, it gives no rows. But I want to get 0 in Totalcount even if EMAIL is not there for that day but can present next day.

It should be like,

Date,Source, Totalcount
15/10/14,Email,0

I used ISNULL function not working as no rows has been resulted.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
user3657257
  • 37
  • 2
  • 9

1 Answers1

1

You could perform a join with a "constant" query. It's an ugly hack, but it should do the trick:

SELECT    c.date, c.source, COALESCE(totalcount, 0)
FROM      (SELECT '2014-10-15' AS date, 'EMAIL' AS source) c
LEFT JOIN (SELECT   date, source, COUNT(*) AS totalcount
           FROM     a
           GROUP BY date, source) a 
       ON a.date = c.date AND a.source = c.source
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Almost there, I think you need an `ISNULL(,)` around `totalcount` – Sev09 Dec 16 '14 at 21:47
  • 1
    @Sev09 yup, kind of missed the mark here :-( I went with `coalesce` which is more ANSI-friendly, but in principal you are correct. – Mureinik Dec 16 '14 at 21:49
  • The asker would be better off using a table with all dates in case there was no data on a certain date (I realize there was in this case). http://stackoverflow.com/questions/6125504/how-can-i-generate-a-temporary-table-filled-with-dates-in-sql-server-2000 – influent Dec 16 '14 at 23:24