SQL needed: sum over values by month

问题:

i have an table with values like this:

count1   count2  count3  month
12        1       4       01/12/2011
6         5       4       23/12/2011
14        6       9       11/06/2011
8         5       4       19/06/2011

How do I get the following results?

count1   count2  count3  month
18        6       8       12
22        11      13      06

回答1:

SELECT SUM(count1), SUM(count2), SUM(count3), MONTH(month)
  FROM myTable
 GROUP BY MONTH(month)

Since MONTH is an SQL Server keyword, you might have to escape the column name month (e.g. [month]), if you date column is really called like that. (Thanks Endy for that comment!)

Also, if data selected span more than one year, you will probably need to group by year as well:

SELECT SUM(count1), SUM(count2), SUM(count3), MONTH(month), YEAR(month)
  FROM myTable
 GROUP BY MONTH(month), YEAR(month)

回答2:

reduce the query burden

SELECT SUM(count1), SUM(count2), SUM(count3), substring(month,4,2)   
FROM myTable  
GROUP BY substring(month,4,2)

回答3:

SELECT SUM(count1), SUM(count2), SUM(count3), MONTH(month)   
FROM Table  
GROUP BY trunc(month,'mm')

This will run in PL/SQL developer :)