SQL needed: sum over values by month

i have an table with values l开发者_Go百科ike 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

---

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

---

reduce the query burden

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

---

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

This will run in PL/SQL developer :)

---

SELECT
SUM(count1) as sum_count1,
to_char(month, 'yyyy-MM') as month
FROM
dataframe 
GROUP BY
month