Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - sum months, quarter, YTD

Posted on 2016-08-13
9
Medium Priority
?
131 Views
Last Modified: 2016-08-31
I have two tables look like a sample below.  I want to sum  table #1 & table #2 - sum by month, also sum each quarter and also sum all months for YTD. The result should look like Table #3. Any help is appreciated


Table # 1
regionid_direct         Jan          Feb    Mar   Apr   May  June   Jul  Aug  Sep  Oct  Nov  Dec

1                                 100          200     500   etc...
2                                 200         500     300   etc...

Table # 2
territoryid_resell    Jan          Feb    Mar   Apr   May  June   Jul  Aug  Sep  Oct  Nov  Dec

1                                 500          600     500   etc...
2                                 300         500     300   etc...


Table #3  = result

id                                Jan          Feb    Mar          Q1           Apr   May  June  Q2   Jul  Aug  Sep  Q3  Oct  Nov  Dec  Q4   YTD

1                                 600          800     1000       2400         etc...
2                                 500         1000     600        2100         etc...
0
Comment
Question by:jagr12
[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
9 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41755457
Since there is no table summation operation you better go about Table #3 in the same way as you created the first two, most probably with a PIVOTing of raw data about sales.

Instead of pivoting SUM(direct) and SUM(resell), you simply SUM(direct+resell).

Without knowing your raw sales data structure, I can't tell you a PIVOTing query, though. Most probably direct sells and resales are not in the same table and same rows, as these sales are unrelated, so you might first union them into one sales column and then aggregate SUM(sales) by month.

Bye, Olaf.
0
 

Author Comment

by:jagr12
ID: 41755479
Something like this?

select   DR.ID,  
					(DR.JAN + RES.JAN) AS Jan
					,(DR.Feb + RES.Feb) AS Feb
					,(DR.Mar + RES.Mar) AS Mar
					,(DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar)  as Q1
					,(DR.Apr + RES.Apr) AS Apr
					,(DR.May + RES.May) AS May
					,(DR.Jun + RES.Jun) AS Jun
					,(DR.Apr + RES.Apr) + (DR.May + RES.May) + (DR.Jun + RES.Jun)  as Q2
					,(DR.Jul + RES.Jul) AS Jul
					,(DR.Aug + RES.Aug) AS Aug
					,(DR.Sep + RES.Sep) AS Sep
					,(DR.Jul + RES.Jul) + (DR.Aug + RES.Aug) + (DR.Sep + RES.Sep)  as Q3
					,(DR.Oct + RES.Oct) AS Oct
					,(DR.Nov + RES.Nov) AS Nov
					,(DR.Dec + RES.Dec) AS Dec
					,(DR.Oct + RES.Oct) + (DR.Nov + RES.Nov) + (DR.Dec + RES.Dec)  as Q4,
					((DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar) + (DR.Apr + RES.Apr) + (DR.May + RES.May) + (DR.Jun + RES.Jun)  + (DR.Jul + RES.Jul) + (DR.Aug + RES.Aug) + (DR.Sep + RES.Sep) + (DR.Oct + RES.Oct) + (DR.Nov + RES.Nov) + (DR.Dec + RES.Dec))  as YTD

						from	 TABLE1 DR, TABLE2  RES
                                                GROUP BY DR.ID
	

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 41755482
If you one row per ID in each table, you don't need GROUP BY.
select   DR.ID,  
					(DR.JAN + RES.JAN) AS Jan
					,(DR.Feb + RES.Feb) AS Feb
					,(DR.Mar + RES.Mar) AS Mar
					,(DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar)  as Q1
					,(DR.Apr + RES.Apr) AS Apr
					,(DR.May + RES.May) AS May
					,(DR.Jun + RES.Jun) AS Jun
					,(DR.Apr + RES.Apr) + (DR.May + RES.May) + (DR.Jun + RES.Jun)  as Q2
					,(DR.Jul + RES.Jul) AS Jul
					,(DR.Aug + RES.Aug) AS Aug
					,(DR.Sep + RES.Sep) AS Sep
					,(DR.Jul + RES.Jul) + (DR.Aug + RES.Aug) + (DR.Sep + RES.Sep)  as Q3
					,(DR.Oct + RES.Oct) AS Oct
					,(DR.Nov + RES.Nov) AS Nov
					,(DR.Dec + RES.Dec) AS Dec
					,(DR.Oct + RES.Oct) + (DR.Nov + RES.Nov) + (DR.Dec + RES.Dec)  as Q4,
					((DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar) + (DR.Apr + RES.Apr) + (DR.May + RES.May) + (DR.Jun + RES.Jun)  + (DR.Jul + RES.Jul) + (DR.Aug + RES.Aug) + (DR.Sep + RES.Sep) + (DR.Oct + RES.Oct) + (DR.Nov + RES.Nov) + (DR.Dec + RES.Dec))  as YTD

						from	 TABLE1 DR
join TABLE2  RES on DR.ID = RES.ID
                                                

Open in new window

If there are multiple records per same ID, use GROUP BY and SUM aggregate function.
select   DR.ID,  
					SUM(DR.JAN + RES.JAN) AS Jan
					,SUM(DR.Feb + RES.Feb) AS Feb
					,SUM(DR.Mar + RES.Mar) AS Mar
					,SUM((DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar)) as Q1
					,SUM(DR.Apr + RES.Apr) AS Apr
					,SUM(DR.May + RES.May) AS May
					,SUM(DR.Jun + RES.Jun) AS Jun
					,SUM((DR.Apr + RES.Apr) + (DR.May + RES.May) + (DR.Jun + RES.Jun))  as Q2
					,SUM(DR.Jul + RES.Jul) AS Jul
					,SUM(DR.Aug + RES.Aug) AS Aug
					,SUM(DR.Sep + RES.Sep) AS Sep
					,SUM(DR.Jul + RES.Jul + DR.Aug + RES.Aug + DR.Sep + RES.Sep) as Q3
					,SUM(DR.Oct + RES.Oct) AS Oct
					,SUM(DR.Nov + RES.Nov) AS Nov
					,SUM(DR.Dec + RES.Dec) AS Dec
					,SUM((DR.Oct + RES.Oct) + (DR.Nov + RES.Nov) + (DR.Dec + RES.Dec)) as Q4,
					SUM(((DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar) + (DR.Apr + RES.Apr) + (DR.May + RES.May) + (DR.Jun + RES.Jun)  + (DR.Jul + RES.Jul) + (DR.Aug + RES.Aug) + (DR.Sep + RES.Sep) + (DR.Oct + RES.Oct) + (DR.Nov + RES.Nov) + (DR.Dec + RES.Dec)))  as YTD

						from	 TABLE1 DR
  JOIN TABLE2  RES ON DR.ID = RES.ID
                                                GROUP BY DR.ID
	

Open in new window

1
Independent Software Vendors: 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!

 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41755484
Well, if that works for you, jagr12. I find it rather cumbersome to put this number of summations together, though it's only 12+4.

You don't join DR and RES correctly, you want to join on same IDs. You don't get 12 rows, but 144, then group by reduces that to 12, but since you summed everything 12 times, you get 12 times too high values, I assume.

Bye, Olaf.
1
 

Author Comment

by:jagr12
ID: 41755487
Is there a better to write  sum for Quarter and YTD? I am just curious.

SUM((DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar)) as Q1


SUM(((DR.JAN + RES.JAN) + (DR.Feb + RES.Feb) + (DR.Mar + RES.Mar) + (DR.Apr + RES.Apr) + (DR.May + RES.May) + (DR.Jun + RES.Jun)  + (DR.Jul + RES.Jul) + (DR.Aug + RES.Aug) + (DR.Sep + RES.Sep) + (DR.Oct + RES.Oct) + (DR.Nov + RES.Nov) + (DR.Dec + RES.Dec)))  as YTD
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41755995
There is a strong liklihood that the most efficient way of meeting your needs exists BEFORE you get to tables 1 and 2.

In other words we only see what you have supplied, but the style of those tables suggests that you have derived them by queries. Those existing queries could be amended to produce the wanted results.
1
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41756141
I second Paul.

As I already said you would do that by PIVOTing raw data, and you couldn't only arrive at monthly data, you could also create quarterly and YTD data at the same step. So advances could be done on the queries creating your current source tables.

Bye, Olaf.
0
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 2000 total points
ID: 41756192
Here's an example how to build this up from scratch:

Declare @sales as Table (Id int, SoldAt Date, Amount Money);

Set Language 'English' 

Insert Into @sales Values (1, '20150105', $1),(2, '20150505', $2), (3, '20150506', $4), (4, '20150807', $8), (5, '20150925', $16),
(6, '20160205', $32),(7, '20160405', $64), (8, '20160408', $128), (9, '20160507', $256), (10, '20160721', $512);

Select * From
(Select [Year], Amount, Period FROM
(Select Year(SoldAt) as [Year]
   ,    Amount
   ,    convert(varchar(3),SoldAt,7) As M
   ,    'Q'+right('0'+convert(varchar(2), datepart(q,SoldAt)), 2) As Q
   ,    'YTD' as Y 
   From @sales) t1
Unpivot (Period For PeriodType In (M, Q, Y)) u) t2
Pivot ( Sum(Amount) For Period IN (Jan, Feb, Mar, Q01, Apr, May, Jun, Q02, Jul, Aug, Sep, Q03, Oct, Nov, Dec, Q04, YTD)
) p

Open in new window


Bye, Olaf.
1
 

Author Comment

by:jagr12
ID: 41763365
thank you gentlemen
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

730 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