Home arrow SQL arrow Page 2 - Creating SQL Reports Based on Date Criteria
SQL

Creating SQL Reports Based on Date Criteria


In this third part of a four-part series covering SQL date calculations, you'll learn how to generate a report based on various date criteria. This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Copyright 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Author Info:
By: O'Reilly Media
Rating: 4 stars4 stars4 stars4 stars4 stars / 18
December 20, 2007
TABLE OF CONTENTS:
  1. · Creating SQL Reports Based on Date Criteria
  2. · Monthly Totals
  3. · Current Month
  4. · Year-to-Date Totals

print this article
SEARCH DEVARTICLES

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

348

 

2006

2

329

 

2006

3

    10

585

 

2006

8

  293 

 

2006

5

413 

 

2006

6

8

465 

 

2006 

206 

 

2006 

9|

456 

 

2006

9

217

 

2006 

10

10

401

 

2006

11

540

 

2006

12

402 

 

2007

1

139

 

2007

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

348 

 

200602 

5

329 

 

200603

10

585 

 

 

200604

8

293 

 

200605

413 

 

200606

465 

 

200607 

6

206 

 

200608

456 

 

200609

4

217

 

200610 

10

401 

 

200611

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.


blog comments powered by Disqus
SQL ARTICLES

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials