Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Year and corresponding sales in each quarter.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Year and corresponding sales in each quarter.
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: 1278
Location: Belgium

PostPosted: Tue Oct 26, 2010 2:57 pm    Post subject:
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    Post subject:
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Oct 26, 2010 6:02 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Wishing everyone a very Happy Diwali ... Rohit Umarjikar General Talk & Fun Stuff 1 Sat Oct 29, 2016 1:54 am
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm
This topic is locked: you cannot edit posts or make replies. Mainframe Openings for 3-10 year exp ... yogeshwar_ade Mainframe Jobs 0 Wed Jan 13, 2016 2:52 pm
No new posts Merry Christmas and Happy New Year! Rohit Umarjikar General Talk & Fun Stuff 3 Thu Dec 24, 2015 3:58 am
No new posts Query a value from the table for the ... sivasaras DB2 2 Wed Feb 04, 2015 9:37 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us