mysql showing null values for group by statements

Question

I'm doing:

   select sum(clicks), 
          date 
     from stats 
 group by date

...however when the sum is null for some date, the whole row is discarded, I want to see: | null | some_date |, how to do so?

Answer 1

It would help to see the full query in question. For every date value that exists in stats, you should either get NULL for the Sum or an integer value. If you are grouping by [Date] and a given date value does not exist, it obviously will not show up. E.g., consider the following test:

Create Table Test ( Clicks int null, [Date] datetime null )
Insert Test(Clicks,[Date]) Values(1,'2010-06-06')
Insert Test(Clicks,[Date]) Values(2,Null)
Insert Test(Clicks,[Date]) Values(3,'2010-06-06')
Insert Test(Clicks,[Date]) Values(4,'2010-06-07')
Insert Test(Clicks,[Date]) Values(4,Null)
Insert Test(Clicks,[Date]) Values(4,'2010-06-07')
Insert Test(Clicks,[Date]) Values(Null,'2010-06-08')

Select T.[Date], Sum(Clicks)
From Test T
Group By T.[Date]

The results should look like:

NULL                       6
2010-06-06 00:00:00.000 4
2010-06-07 00:00:00.000 8
2010-06-08 00:00:00.000 NULL

Note I still get a row even when Sum(Clicks) is null. Is it that you are joining this information to something else on the Sum(Clicks) calculation?