SQL Query

Sriv
Sriv used Ask the Experts™
on
I have the below scenario. I want to sum values for each qurter and show it at the beginning quarter month. What is the best approach ?


January      February      March      April      May      June      July      August      September      October      November      December
50      100      50      10      50      50      50      50      50      50      50      50
I want to Show                                                                   
January      February      March      April      May      June      July      August      September      October      November      December
200      0      0      110      0      0      150      0      0      150      0      0
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
Is this acceptable?
Name                       qrt
1 Q1 sum	0	0	200
1. J-F-M	50	100	50
1. J-F-M	200	0	0
2 Q2 sum	0	0	110
2. A-M-J	10	50	50
2. A-M-J	110	0	0
3 Q3 sum	0	0	150
3. A-M-J	50	50	50
3. A-M-J	150	0	0
4 Q4 sum	0	0	150
4. O-N-D	50	50	50
4. O-N-D	150	0	0

Open in new window


Test table and the solution:
use ee
--drop table #q
create table #Q(Jan  int, Feb int, Mar int, Apr int, May  int, Jun  int, Jul int, Aug int, Sep int, Oct int, Nov int, Dec int);
insert into #Q(Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec) Values
(50,  100, 50, 10, 50, 50, 50, 50, 50, 50, 50, 50)
,(200,  0, 0, 110, 0, 0, 150, 0, 0, 150, 0, 0)

Select * From #q;

;with cte
as
(
select *,
Jan+Feb + Mar as q1
, Apr + May  + Jun as q2
, Jul + Aug + Sep as q3
, Oct + Nov + Dec as q4
from #Q
)
Select '1 Q1 sum' Name,'-', '-', q1 quarter from CTE
union select '1. J-F-M' Name, Jan, Feb, Mar from CTE
union Select '2 Q2 sum' Name,'-', '-', q2 quarter from CTE
union select '2. A-M-J' Name, Apr, May, Jun from CTE
union Select '3 Q3 sum' Name,'-', '-', q3 quarter from CTE
union select '3. A-M-J' Name, Jul, Aug, Sep from CTE
union Select '4 Q4 sum' Name,'-', '-', q4 quarter from CTE
union select '4. O-N-D' Name, OCt, Nov, Dec from CTE
Order By Name

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Just sum the month columns, quarter by quarter:
SELECT January+February+March AS Q1,
	April+May+June AS Q2,
	July+August+September AS Q3,
	October+November+December AS Q4
FROM TableName

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial