Solved

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

Posted on 2014-03-06
11
1,252 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
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
 
LVL 40

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…

863 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

20 Experts available now in Live!

Get 1:1 Help Now