Creating SQL Reports Based on Date Criteria - Monthly Totals (Page 2 of 4 )
If you want to see monthly totals, you must include the year and the month in theGROUP BYexpression:
mysql> SELECT YEAR(whn), MONTH(whn), COUNT(v), SUM(v) ->FROM t ->GROUP BY YEAR(whn),MONTH(whn);
YEAR(whn) MONTH(whn) COUNT(v)SUM(v)
2006
1
7
348
2006
2
5
329
2006
3
10
585
2006
4
8
293
2006
5
7
413
2006
6
8
465
2006
7
6
206
2006
8
9|
456
2006
9
4
217
2006
10
10
401
2006
11
9
540
2006
12
7
402
2007
1
2
139
2007
2
13
800
2007
3
14
674
2007
4
6
456
2007
5
4
171
In MySQL and PostgreSQL, you can implicitly cast a date to a string and you can use that to extract the year and month. For example:
SELECT SUBSTRING(whn,1,7), COUNT(v), SUM(v) GROUP BY SUBSTRING(whn,1,7)
You can combine the year and month into a single number if you want. If you multiply the year by 100 and add the month you can be certain that each month will be distinct and sortable. Also, the resulting number is human readable and is suitable for processing as a string; you can easily turn it back into a date [Hack #19]:
mysql> SELECT 100*YEAR(whn)+MONTH(whn), COUNT(v), SUM(v) ->FROM t ->GROUP BY 100*YEAR(whn)+MONTH(whn);
100*YEAR(whn)+MONTH(whn) COUNT(v) SUM(v)
200601
7
348
200602
5
329
200603
10
585
200604
8
293
200605
7
413
200606
8
465
200607
6
206
200608
9
456
200609
4
217
200610
10
401
200611
9
540
200612
7
402
200701
2
139
200702 |
13
800
200703 |
14
674
200704 |
6
456
200705 |
4
171
MySQL, Access, and SQL Server support the nonstandard functionsMONTHandYEAR; Oracle does not.
Oracle and MySQL support the SQL92 functionEXTRACT, as inEXTRACT(MONTH FROM whn). Oracle also has theTO_DATEfunction, so it allowsTO_DATE(whn, 'yyyymm'), for example.
SQL 
/ 10 
SQL Date Handling and Data Trends
