Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql showing null values for group by statements

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?

like image 827
khelll Avatar asked Jun 11 '10 12:06

khelll


People also ask

Does GROUP BY clause ignore NULL values in MySQL?

Aggregate (group) functions such as COUNT() , MIN() , and SUM() ignore NULL values. The exception to this is COUNT(*) , which counts rows and not individual column values. For example, the following statement produces two counts.

How do you handle NULL values in GROUP BY?

If the grouping column contains a null value, that row becomes its own group in the results. If the grouping column contains more than one null value, all null values form a single group.

Does GROUP BY group NULLs?

GROUP BY does treat all NULL values equally.

Does GROUP BY ignore NULL values?

Group functions ignore the NULL values in the column. To enforce the group functions ti include the NULL value, use NVL function.


1 Answers

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?

like image 91
Thomas Avatar answered Oct 02 '22 07:10

Thomas