Group by year and month based on join of two tables with report dates

nbotts
nbotts used Ask the Experts™
on
I'm joining two tables of information that both have a datetime field called "report_date".

I'd like to group the results by year and month but still need to include the report_date field from at least one of the tables for the purpose of drawing out a chart. The actual day or time of the report_date doesn't matter, just the month and year.

My current query looks something like this but certainly is incorrect:

SELECT
YEAR(`usage`.`report_date`) as report_year,
MONTH(`usage`.`report_date`) as report_month,
`usage`.`number` as usage,
 `authorizations`.`number` as authorizations
 `usage`.`report_date` as report_date
FROM
 `database`.`usage`
 INNER JOIN `database`.`authorizations` 
 ON (`usage`.`site` = `authorizations`.`site`)
 GROUP BY report_year, report_month

Open in new window


In the end I would have something like:

report_year	report_month	report_date	usage	authorizations
      2014	                 1	        2014-01-05	   15	               14
      2014	                 2	        2014-02-17	    20	       21
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you want to know usage/authorizations on a daily-basis then include report_date in the group-by-clause (example below), otherwise, exclude it from the group-by-clause and include it in an aggregate-function in the select-clause (maybe have two, the earliest report_date in that month and the latest report_date in that month).  Fields missing from the group-by-clause needs to be in an aggregate-function.
SELECT	YEAR(a.report_date) as report_year
,	MONTH(a.report_date) as report_month
,	a.report_date as report_date
,	SUM(a.number) as usage
,	SUM(b.number) as authorizations
FROM	usage		a
JOIN	authorizations	b	ON a.site = b.site
GROUP
BY	YEAR(a.report_date)
,	MONTH(a.report_date)
,	a.report_date

Open in new window

Author

Commented:
Thanks John,

I want totals to be aggregated by month, sorry if I was unclear. I was able to get the query to run, but the totals were wrong.

Attached is some sample data if that helps.

The query that works if I just wanted to pull from usage is:

select year(`usage`.`report_date`) AS `year`,month(`usage`.`report_date`) AS `month`,`usage`.`report_date` AS `report_date`,sum(`usage`.`number`) AS `usage_num` from `usage`
group by year(`usage`.`report_date`),month(`usage`.`report_date`)

Open in new window


Maybe that will help clarify what I am trying to do.
usage-authorizations.sql

Author

Commented:
Any other help out there?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SharathData Engineer

Commented:
try this.
SELECT YEAR(`usage`.`report_date`)    AS report_year, 
       MONTH(`usage`.`report_date`)   AS report_month, 
       SUM(`usage`.`number`)          AS "usage", 
       SUM(`authorizations`.`number`) AS authorizations, 
       `usage`.`report_date`          AS report_date 
  FROM `usage` 
       INNER JOIN `authorizations` 
               ON ( `usage`.`site` = `authorizations`.`site` ) 
 GROUP BY MONTH(`usage`.`report_date`) 

Open in new window

http://sqlfiddle.com/#!2/d6330/14

Author

Commented:
Thank you Sharath, all looks exactly how I want it reported, except the count appears to be wrong.

For March and April the totals should be 2 for both and then May 2014 should have one for Authorizations and May 2015 should have 1 for Usage.

Author

Commented:
Any follow-up from anyone?

This seems close...
Hi,

What you need is sum usage individuals and authorization individuals. So JOINs would not be the right course here.

I would suggest UNION the two tables (marking as 0 the field that does not exist to the table) and then SUMming the resulted table.

Here is my representation in SQL Fiddle.

In case SQL Fiddle is Unavailable this is the code:
SELECT  `report_year`,
        `report_month`,
        SUM(`usage`) AS "usage",
        SUM(`authorizations`) AS authorizations,
        MIN(`report_date`) AS report_date
FROM
(
  SELECT YEAR(`usage`.`report_date`)    AS report_year,
         MONTH(`usage`.`report_date`)   AS report_month,
         `usage`.`number`          AS "usage",
         0 AS authorizations,
         `usage`.`report_date`          AS report_date
  FROM   `usage`
  UNION ALL
  SELECT YEAR(`authorizations`.`report_date`)    AS report_year,
         MONTH(`authorizations`.`report_date`)   AS report_month,
         0 AS "usage",
         `authorizations`.`number` AS authorizations,
         `authorizations`.`report_date`          AS report_date
  FROM   `authorizations`
) AS T
GROUP BY `report_year`,`report_month`

Open in new window


And the results i get:

REPORT_YEAR 	REPORT_MONTH 	USAGE 	AUTHORIZATIONS 	REPORT_DATE
2014 	 	3 	 	2 	2 	 	March, 15 2014 00:00:00+0000
2014 	 	4 	 	2 	2 	 	April, 23 2014 00:00:00+0000
2014 	 	5 	 	0 	1 	 	May, 21 2014 00:00:00+0000
2015 	 	5 	 	1 	0 	 	May, 23 2015 15:26:00+0000

Open in new window


Giannis

Author

Commented:
Thank you! That does the trick and the results came out right when applied to the real data.

Much appreciated Giannis

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial