按月汇总SQL查询分组 [英] Aggregate SQL query grouping by month

问题描述

我有一个按小时，每天和每月间隔记录的事务处理数据库(Access 2007).我想以一种有意义的方式查看它们(而不是按小时存储能耗，而是希望将其存储在2011年1月的所有月份中).我要操作的表具有以下常规格式:

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:

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

我将如何去做?交易日期可以是任何日期，交易时间可以是1-24.查询本身似乎并不难，类似于以下内容:

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:

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)?

任何帮助将不胜感激

推荐答案

似乎您需要按月份和年份进行分组.否则，您将合并2010年1月和2011年1月:

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:

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)

我不知道您的SQL版本是否具有MONTH和YEAR函数，因此您可能必须使用DATEPART.

I don't know if your version of SQL has the MONTH and YEAR functions, so you may have to use DATEPART.

这篇关于按月汇总SQL查询分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！