Solved

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

Posted on 2014-03-06
11
1,221 Views
Last Modified: 2014-03-11
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
Comment
Question by:nbotts
11 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
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
 

Author Comment

by:nbotts
Comment Utility
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
 

Author Comment

by:nbotts
Comment Utility
Any other help out there?
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:nbotts
Comment Utility
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
 

Author Comment

by:nbotts
Comment Utility
Any follow-up from anyone?

This seems close...
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 400 total points
Comment Utility
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
 

Author Closing Comment

by:nbotts
Comment Utility
Thank you! That does the trick and the results came out right when applied to the real data.

Much appreciated Giannis
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
Anytime :)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now