Solved

SQL - sum months, quarter, YTD

Posted on 2016-08-13
9
91 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
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.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query logins with details regarding database users and privilege level 4 35
SQL Query 34 82
Session that filled up my transaction logs 1 40
append to an ms access field 6 26
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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

24 Experts available now in Live!

Get 1:1 Help Now