按月聚合 SQL 查询分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7011619/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Aggregate SQL query grouping by month
提问by Tom
I have a database of Transactions (Access 2007) that are recorded in hourly, daily and monthly intervals. I would like to view them in a meaningful way (instead of hour-by-hour energy usage, which is how it's stored, I want all usage for the month of January, 2011). The tables that I want to operate on have this general format:
我有一个交易数据库（Access 2007），以每小时、每天和每月的时间间隔记录。我想以一种有意义的方式查看它们（而不是按小时计算能源使用量，这是它的存储方式，我想要 2011 年 1 月的所有使用量）。我要操作的表具有以下通用格式：
CustID|CustomerCode|TransactionDate|(Transaction Hour depending on table)|Usage
So If I want to take a table like that and make a view that looked something like this
所以如果我想拿一张这样的桌子并制作一个看起来像这样的视图
BillingPeriod|Usage(mWh)
1/2011 |500
2/2011 |600
3/2011 |700
etc
How would I go about doing that? The transaction dates can be any date, and the transaction hours can be 1-24. The query itself doesn't seem that hard, something along the lines of:
我该怎么做？交易日期可以是任意日期，交易时间可以是1-24。查询本身似乎并不难，大致如下：
SELECT TransactionDate, SUM(Usage)
FROM UsageTable
Where (TransactionDate Between [Some Start Date] AND[Some End Date])
GROUP BY TransactionDate;
The problem is formatting. I obviously can't group by transactiondate for my desired results, I just wrote it so the query was semantically correct. Maybe I could do something like
问题是格式化。我显然不能按交易日期分组以获得我想要的结果，我只是写了它，所以查询在语义上是正确的。也许我可以做类似的事情
SELECT Mid(TransactionDate,0,2) + Mid(TransactionDate, 6, 4)?
Any help would be appreciated
任何帮助，将不胜感激
回答by Narnian
It seems that you would need to group by both the month and the year. Otherwise, you'll have January 2010 and January 2011 combined:
似乎您需要按月份和年份进行分组。否则，您将合并 2010 年 1 月和 2011 年 1 月：
SELECT YEAR(TransactionDate), MONTH(TransactionDate), SUM(Usage)
FROM YourTable
WHERE (TransactionDate Between [Some Start Date] AND[Some End Date])
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
ORDER BY YEAR(Created), MONTH(Created)
I don't know if your version of SQL has the MONTH and YEAR functions, so you may have to use DATEPART.
我不知道你的 SQL 版本是否有 MONTH 和 YEAR 函数，所以你可能必须使用 DATEPART。
回答by MatBailie
So as to Avoid conversion to strings, concatenations and conversion back to dates, use DATEADD() and DATEDIFF().
为了避免转换为字符串、连接和转换回日期，请使用 DATEADD() 和 DATEDIFF()。
SELECT
DATEADD("m", DATEDIFF("m", 0, TransactionDate), 0) AS TransactionMonth,
SUM(Usage) AS TotalUsage
FROM
yourTable
WHERE
TransactionDate BETWEEN <startDate> AND <endDate>
GROUP BY
DATEADD("m", DATEDIFF("m", 0, TransactionDate), 0)
ORDER BY
DATEADD("m", DATEDIFF("m", 0, TransactionDate), 0)
回答by Patrick Honorez
I generally use Format([TransactionDate], "yyyy-mm")because it's simple and sorts well.
As another option, you could use [TransactionDate]-Day([TransactionDate])+1, which will move every date to the first of its month. THe advantage is that you can still easily format that any way you want, or group that by quarter or year afterwards.
我通常使用Format([TransactionDate], "yyyy-mm")它，因为它简单且分类良好。
作为另一种选择，您可以使用[TransactionDate]-Day([TransactionDate])+1，它将每个日期移动到其月份的第一天。优点是您仍然可以轻松地以任何您想要的方式格式化，或者在之后按季度或年度对其进行分组。
回答by Baaju
SELECT MONTH(TransactionDate),YEAR(TransactionDate), SUM(Usage)
FROM UsageTable
Where (TransactionDate Between [Some Start Date] AND[Some End Date])
GROUP BY MONTH(TransactionDate),YEAR(TransactionDate);
回答by jlb
something like...
就像是...
SELECT Month(UsageTable.TransactionDate) & '/' & Year(UsageTable.TransactionDate) AS BillingPeriod, Sum(UsageTable.Usage) AS Usage
FROM UsageTable
WHERE (((UsageTable.TransactionDate) Between [Some Start Date] And [Some End Date]))
GROUP BY Month(UsageTable.TransactionDate) & '/' & Year(UsageTable.TransactionDate);
