• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1473
  • Last Modified:

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

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
0
nbotts
Asked:
nbotts
1 Solution
 
John_VidmarCommented:
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

0
 
nbottsAuthor 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
0
 
nbottsAuthor Commented:
Any other help out there?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SharathData EngineerCommented:
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
0
 
nbottsAuthor 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.
0
 
nbottsAuthor Commented:
Any follow-up from anyone?

This seems close...
0
 
Ioannis ParaskevopoulosCommented:
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
0
 
nbottsAuthor Commented:
Thank you! That does the trick and the results came out right when applied to the real data.

Much appreciated Giannis
0
 
Ioannis ParaskevopoulosCommented:
Anytime :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now