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.

Next: Current Month >>
 

More SQL Articles
More By O'Reilly Media

Comments On: Creating SQL Reports Based on Date Criteria

· This article is an excerpt from the book "SQL Hacks," published by O'Reilly. We hope...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.