Geek Answers Handbook

Sum of column columns by date

It should be simple enough, but something crossed my mind.

All I have is a two-column table, 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

etc.

I want to calculate the total number of words for each day . I group them by date and select the amount of WordCount and finally get a syntax error (wordcount should be in the group by condition), but now I get null for the daily account

This is my request:

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

this is only a null value. How can I find out what is wrong?

thanks.

+4
source share
2 answers

What to do if you use:

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

I do not understand why you are also grouped by the number of words ....

Also, since DateAdded most likely a DATETIME column, including the temporary part, you might want to group only 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 displays the result:

 2008-11-07 128 2008-11-23 25 2008-12-03 62
+7
source

I think this should give you the number of words per day

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

More articles:


All Articles