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: 1278
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 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
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


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