Sum of one column group by date column

问题:

This should be simple enough but something's gotten me big time.

All I have is a table with just TWO columns, something like:

 WordCount          DateAdded
 `````````````````````````````
 96                 2008-11-07 09:16:31.810
 32                 2008-11-07 15:26:27.547
 25                 2008-11-23 16:05:39.640
 62                 2008-12-03 12:33:03.110

and so on.

I want to calculate the total word count for each day - I group them by dateadded and select sum of WordCount and finally get the syntax error (wordcount has to be in group by clause) but now I am getting nulls for day's count

This is my query:

select SUM(WordCount) as 'words per day' from @WordsCount group by DateAdded, WordCount

this is selecting just null. How can I know what is wrong?

thanks.

回答1:

What if you use:

select SUM(WordCount) as 'words per day' 
from @WordsCount 
group by DateAdded

I don't see why you're also grouping by the word count....

Also, since the DateAdded likely is a DATETIME column including a time portion, you might want to group by just the date:

select SUM(WordCount) as 'words per day' 
from @WordsCount 
group by CAST(DateAdded AS DATE)

Update: if I try this, the query works just fine ....

DECLARE @WordsCnt TABLE (WordCount INT, DateAdded DATETIME)

INSERT INTO @wordsCnt(WordCount, DateAdded)
VALUES(96, '2008-11-07 09:16:31.810'),
      (32, '2008-11-07 15:26:27.547'),
      (25, '2008-11-23 16:05:39.640'),
      (62, '2008-12-03 12:33:03.110')

select CAST(DateAdded AS DATE), SUM(WordCount) as 'words per day' 
from @WordsCnt
group by CAST(DateAdded AS DATE)

and produces the output:

2008-11-07   128
2008-11-23    25
2008-12-03    62

回答2:

I think this should give you word count per day

select      SUM(WordCount) as 'words per day' , cast(DateAdded as date) dateAdded
from        WordsCount 
group by    cast(DateAdded as date)