Mysql showing null values ​​for operator group

Question

I do:

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

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

Answer 1

This will help to see the full request. For each date value that exists in stats , you must either get NULL for the sum or integer value. If you group [Date] and the given date value does not exist, it obviously will not be displayed. For example, 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 this:

 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 the string even when Sum(Clicks) is NULL. Is it that you attach this information to something else when calculating Sum(Clicks) ?

Answer 2

Since you are making a group by date, I assume that you have more than one record per date. I think you will have to ifnull the source data, or you will lose some.

For example, if on June 10th there were 10, 20, 30 and null, you would get null instead of 60.

I think this will prevent this problem:

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