[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

SQL - sum months, quarter, YTD

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
jagr12
Asked:
jagr12
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
jagr12Author Commented:
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
 
SharathData EngineerCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Olaf DoschkeSoftware DeveloperCommented:
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
 
jagr12Author Commented:
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
 
PortletPaulCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
jagr12Author Commented:
thank you gentlemen
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now