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
 
GROUP BY problem...

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

New User


Joined: 23 Jul 2008
Posts: 29
Location: Little Rock - Arkansas

PostPosted: Wed Aug 19, 2009 7:09 pm    Post subject: GROUP BY problem...
Reply with quote

SELECT ICN, REL_LINE_NBR, PAYMENT_AMT, CONTRACT_NBR, CLEAN_CLAIM_IND, CLAIM_TYPE, ASSIGN_CODE, EMC_INDIC, CLAIM_LINE_STATUS, MAX (PAID_DATE) PAID, MIN (CLEAN_DATE) CLEAN, MIN (CLAIM_RECEIVED_DT) RECEIVE, COUNT(*) CTR
FROM DSNW.DWS_US_AMISYS_CLM

GROUP BY ICN, REL_LINE_NBR, PAYMENT_AMT, CONTRACT_NBR, CLEAN_CLAIM_IND, CLAIM_TYPE, ASSIGN_CODE, EMC_INDIC, CLAIM_LINE_STATUS

ORDER BY ICN

WITH UR

Question :- The above is my sql query, it is having min, max functions and at the same time i am doing GROUP BY based on some columns, here if i give GROUP BY on some columns only which are present in SELECT query its giving message GROUPING INCONSISTENT, but in GROUP BY if i give all the columns present in SELECT QUERY then it is working fine.

So in a GROUP BY clause is it mandatory to give all the columns present in SELECT query which are having aggregate functions like (max,min)???

Thx,
Satish
Back to top
View user's profile Send private message

bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Wed Aug 19, 2009 7:27 pm    Post subject: Reply to: GROUP BY problem...
Reply with quote

Can you please let us know what is your exact requirment
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Wed Aug 19, 2009 7:33 pm    Post subject:
Reply with quote

Hi satish,

Whichever coloumn is not included in group by and is there in select clause should have a aggregate function associated with it

Please let me know if you need further clarification
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Aug 20, 2009 10:43 am    Post subject:
Reply with quote

Please go thru the link Link1
Link2
Link2
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Thu Aug 20, 2009 4:08 pm    Post subject: Reply to: GROUP BY problem...
Reply with quote

Hi Sathish

You have to include all columns in the SELECT clause in the GROUP BY clause except the columns included in the aggregate function. It is mandatory.

For Example.

SELECT DEPTNO, SEX, AVG(SALARY), MIN(SALARY), MAX(BONUS) FROM EMP GROUP BY DEPTNO, SEX ORDER BY MIN(SALARY)

Hope this will help you

Regards
Raghu
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 LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
This topic is locked: you cannot edit posts or make replies. Limit access to certain RACF group cvnlynn CLIST & REXX 5 Wed Aug 23, 2017 2:28 am
No new posts Problem commiting to DB2 from IMS MPP Ole Soerensen IMS DB/DC 2 Wed Jul 26, 2017 5:19 pm
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am

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