按日期列分组的一列的总和 [英] 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

等等.

我想计算每天的总字数 - 我按添加日期对它们进行分组并选择 WordCount 的总和，最后得到语法错误(wordcount 必须在 group by 子句中)但现在我的天数为空

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

这是我的查询:

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?

谢谢.

推荐答案

如果你使用:

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....

此外，由于 DateAdded 可能是包含时间部分的 DATETIME 列，您可能只想按日期分组:

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)

并产生输出:

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

这篇关于按日期列分组的一列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！