Results 1 to 4 of 4

Thread: Group By

  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Group By

    I am trying to view orders based on which quarter they were made in, but having troubles with the group by function.

    This is what I am trying:

    SELECT to_char(DUEDATE,'YYYY') "YEAR", to_char (DUEDATE,'Q') "Q"
    FROM ORDER
    group by to_char (DUEDATE,'Q')

    I receive the following error:

    Error: ORA-00979: not a GROUP BY expression

    If i remove the group by expression i get a list of all the orders with the correct data next to them, but I NEED it to group on quarter.

    Can anyone tell me whatI'm doing wrong?

  2. #2
    Join Date
    Feb 2004
    Posts
    2

    DONE it!!

    Dont wory! Done it. Anyone searching the net for the same thing, the solution was this:


    SELECT to_char (DUEDATE,'YYYY') "YEAR", to_char (DUEDATE,'Q') "Q", COUNT(DUEDATE) "ORDERS"
    FROM S_ORDER
    group by to_char (DUEDATE,'YYYY'),to_char (DUEDATE,'Q')
    GO

    I had to group by both year and quater as that was what I was selecting!

    Hope that makes sense

    Mark

  3. #3
    Join Date
    Jan 2004
    Posts
    492

    Re: DONE it!!

    You also missed the COUNT statement in your first post...you can only group when using aggregate functions such as min..max..etc.

    Glad to hear you got it working though

  4. #4
    Join Date
    Feb 2004
    Posts
    18

    Re: DONE it!!

    Originally posted by ss659
    You also missed the COUNT statement in your first post...you can only group when using aggregate functions such as min..max..etc.

    Glad to hear you got it working though
    Hey ss659.. .A small correction
    It is not necessary that an aggregate function must be used when using the group by clause... The only thing that is required is that all the columns in the select clause must appear in the group by... however the column used in an aggregate function need not be in the group by

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •