sql月度分组_按月汇总SQL查询分组

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

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

Any help would be appreciated

解决方案

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)

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