6

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.

Improve this question

2 Answers 2

Reset to default
13

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

tsql is complaining that select clause cannot have WordCount as it's not contained in the group by clause
Oh, I just realized I am just selecting WordsCnt instead of SUM(wordscnt).. Added sum and it works!! thanks!
wonder why it complains when sum isn't included? first I tried to select all rows, and then adding sum, so whne i tried without sum it gave error, so I had to add it to group by clause..
@LocustHorde: well if you have the WordCount without the SUM(...) in there - then OF COURSE it will complain! That's the whole point of a GROUP BY - either your column must be in the GROUP BY clause, or it must be aggregated by a SUM(), MIN(), MAX(), AVG() function......
You still helped me, since I am still learning a lot, this was very usefull!
7

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)
Improve this answer

1 Comment

Thank you, I realized the error was that I had included word count in group by clause too!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.