Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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: 6968
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: 1281
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 Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
No new posts 2 byte YEAR from Y'DATE2' field tecnokrat DFSORT/ICETOOL 2 Tue Feb 07, 2017 12:05 pm
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

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us