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?
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.
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.
GROUP BY does treat all NULL values equally.
Group functions ignore the NULL values in the column. To enforce the group functions ti include the NULL value, use NVL function.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With