GROUP BY problem...

satish kanamarlapudi · Posted: Wed Aug 19, 2009 7:09 pm

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

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

Can you please let us know what is your exact requirment

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

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

Ketan Varhade · Posted: Thu Aug 20, 2009 10:43 am

Please go thru the link Link1
Link2
Link2

Raghu navaikulam · Posted: Thu Aug 20, 2009 4:08 pm

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