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
 

 

Inconsitent grouping when using Sum, Case, Group by in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sravs

New User


Joined: 20 Jul 2009
Posts: 1
Location: Hyderabad

PostPosted: Wed Jun 08, 2011 8:15 pm    Post subject: Inconsitent grouping when using Sum, Case, Group by in DB2
Reply with quote

Hi,

I am trying to execute the below cursor and getting the error message 'Inconsistent Grouping'.

Code:
SELECT COL1, B.COL2,
         SUM(CASE COL5 WHEN 'AAAAA' THEN 1               
                                  ELSE 0 END ) AS COVG,
         CASE COL5 WHEN 'AAAAA' THEN COL6
                           ELSE ' ' AS FORM
FROM  TABLE1 A,
          TABLE2 B
WHERE A.COL1 = B.COL1
GROUP BY COL1, B.COL2
WITH UR
;

I want to display the value of COL6 when value in the COL5 is equal to 'AAAAA' for a particular row fetched from TABLE2.

Please help me.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Wed Jun 08, 2011 9:26 pm    Post subject:
Reply with quote

well that's exactly what it is.
btw, you missed an END when copying.

Code:
SELECT COL1, B.COL2,
         SUM(CASE COL5 WHEN 'AAAAA' THEN 1               
                                  ELSE 0 END ) AS COVG,
         CASE COL5 WHEN 'AAAAA' THEN COL6
                           ELSE ' ' END AS FORM
FROM  TABLE1 A,
          TABLE2 B
WHERE A.COL1 = B.COL1
GROUP BY COL1, B.COL2 ,CASE COL5 WHEN 'AAAAA' THEN COL6 ELSE ' ' END
WITH UR

will give you another result. but then the sum COVG probably isn't what you want
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 Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 2 Wed Mar 22, 2017 7:46 am
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Sorting group data rajella DFSORT/ICETOOL 4 Sun Jan 22, 2017 11:32 pm
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am


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