IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Year and corresponding sales in each quarter.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
shikhagupta33

New User


Joined: 11 Apr 2005
Posts: 7
Location: Bangalore

PostPosted: Tue Oct 26, 2010 2:08 pm
Reply with quote

I have a table say:
YEAR QTR SALES
2009 Q1 5000
2009 Q2 6000
2009 Q3 4000
2009 Q4 7000
2010 Q1 9000
2010 Q2 5000
2010 Q3 4000
2010 Q4 8000

I want a result set like
YEAR Q1 Q2 Q3 Q4
2009 5000 6000 4000 7000
2010 9000 5000 4000 8000

What will be the sql query to achieve above result set?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Oct 26, 2010 2:57 pm
Reply with quote

Code:
select year,
   sum(case when qtr = 'Q1' then sales else 0 end) as q1,
   sum(case when qtr = 'Q2' then sales else 0 end) as q2,
   sum(case when qtr = 'Q3' then sales else 0 end) as q3,
   sum(case when qtr = 'Q4' then sales else 0 end) as q4
from Tab1
group by year
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 26, 2010 3:53 pm
Reply with quote

thx GuyC
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Oct 26, 2010 6:02 pm
Reply with quote

dbzTHEdinosauer wrote:
thx GuyC
urwc.
Didn't want to waste time slowfeeding, because I've seen too many terrible constructs (performance wise) to solve this.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to extract the data for current y... DFSORT/ICETOOL 8
No new posts Vision plus req. In Pune 5 to 10 year Mainframe Jobs 0
No new posts Sales Engineer - IBM i opening with R... Mainframe Jobs 0
No new posts Senior Partner Sales Manager, Rocket ... Mainframe Jobs 0
No new posts Fetch Previous month & year in MM... DFSORT/ICETOOL 3
Search our Forums:

Back to Top