Solved

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

Posted on 2014-03-06
11
1,369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39910386
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
ID: 39910576
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
ID: 39912495
Any other help out there?
0
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 
LVL 41

Expert Comment

by:Sharath
ID: 39913938
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
 

Author Comment

by:nbotts
ID: 39914607
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
ID: 39916346
Any follow-up from anyone?

This seems close...
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 400 total points
ID: 39919896
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
ID: 39922003
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
ID: 39922045
Anytime :)
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

630 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