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

Since you are doing a group by date, I assume you have more than one record per date. I would think you'd have to ifnull on the original data, or you will lose some.

For example, if Jun 10th had counts of 10, 20, 30 and null, you'd get null instead of 60.

I think this will prevent that issue:

select ifnull(sum(ifnull(clicks,0)), 'null'), date from stats group by date;

Answer 2

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?