#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2004
    Posts
    35
    Rep Power
    11

    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;
  2. #2
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    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 02:36 PM.
    ~Nemi

    Before posting did you try:
    [ Javadocs | Google ]
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Posts
    1
    Rep Power
    0
    Originally Posted by Nemi
    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.
    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.

IMN logo majestic logo threadwatch logo seochat tools logo