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
 

 

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: 186
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 Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 2 Wed Mar 22, 2017 7:46 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts Sorting group data rajella DFSORT/ICETOOL 4 Sun Jan 22, 2017 11:32 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