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
Medium Priority
131 Views
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
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

LVL 30

Expert Comment

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

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

``````
0

LVL 41

Expert Comment

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

``````
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

``````
1

LVL 30

Expert Comment

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

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

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

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

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
``````

Bye, Olaf.
1

Author Comment

ID: 41763365
thank you gentlemen
0

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month11 days, 12 hours left to enroll