Solved

SQL - sum months, quarter, YTD

Posted on 2016-08-13
9
81 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
9 Comments
 
LVL 29

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 40

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
 
LVL 29

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 48

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 29

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 29

Accepted Solution

by:
Olaf Doschke earned 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
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…

759 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

23 Experts available now in Live!

Get 1:1 Help Now