Where do i put the group by clause?
can someone tell me where i should put the group by clause in this sql statement. i want to group by et.description (it is a varchar (256) ).
thanks a lot
SELECT IP.PAYEE_NAME, E.POST_FILE_ITEM_ID, P.AMOUNT, P.SERIAL_NO, P.ITEM_SEQUENCE_NO, ET.DESCRIPTION
FROM ISSUE_FILE_ITEM_PAYEE IP, EXCEPTION E, POST_FILE_ITEM P, POST_FILE_BANK_ACCOUNT A,
ISSUE_FILE_ITEM IT, EXCEPTION_TYPE ET
WHERE E.POST_FILE_ITEM_ID = P.POST_FILE_ITEM_ID
AND P.PROCESSING_CYCLE_DATE BETWEEN '****-**-**' AND '****-**-**'
AND A.ACCOUNT_NO= '*******'
AND P.POST_FILE_BANK_ACCOUNT_ID = A.POST_FILE_BANK_ACCOUNT_ID
AND P.ISSUE_FILE_ITEM_ID = IP.ISSUE_FILE_ITEM_ID
AND P.ISSUE_FILE_ITEM_ID = IT.ISSUE_FILE_ITEM_ID
AND E.EXCEPTION_TYPE_ID = ET.EXCEPTION_TYPE_ID;
A GROUP BY clause is used when you have an aggregate function (SUM, COUNT, etc.). It is used when you don't want to retrieve a value associated with a single record but you want to retrieve a value related to a group of records.
Since you don't have any aggregate functions in your query, a GROUP BY clause doesn't accomplish anything. You could add an ORDER BY clause as the last line if you wanted to have all the descriptions in order so identical values would be "grouped" together if that was your intention.
July 10th, 2004, 08:16 AM
excellent answer, dcaillouet, which i would like to add a few words to
it is permissible to have a GROUP BY without an aggregate function
(it is also permissible to have an aggregate function without a GROUP BY -- the famous example is "select count(*) from mytable" where the entire table is the group)
a GROUP BY without aggregate functions accomplishes the same as SELECT DISTINCT
July 10th, 2004, 03:21 PM
I believe you can do a GROUP BY with non-aggregate functions if you specify each column you want to group by. In other words, you would have to list all the columns that you have in the SELECT clause in the GROUP BY clause, which means they would not group unless all columns are the same, not just the description field.
And to answer the original question, the GROUP BY clause goes after your WHERE clause.
Last edited by Nemi; July 10th, 2004 at 03:36 PM.
April 8th, 2006, 06:16 AM
We can over come this limitaion by using "Over-Partition by" command on each of the columns in the select query. though this might reduce the query performance a bit.
Originally Posted by Nemi