DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Correct me...

  1. #1
    Join Date
    Jun 2001
    Posts
    33
    Please correct me if I am wrong:

    1. columns used in the ORDER BY clause should be part of SELECT clause

    2. columns used in the ORDER BY clause should be first part of the GROUP BY clause.

    select id_number, description, sum(price) from inventory
    where price > 0
    group by id_nubmer
    order by manufacturer_id

    Which line causes error. I am guessing it is last line. Any suggesetions..

    nath

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    1. No
    2. No

    The line that is causing an error is not the last line, it is the third line.

    It should be "GROUP BY id_number, description" or "GROUP BY description, id_number"
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    To generalize what jmodic said, realize the question you are asking the database:

    For each id_number and description, what is the sum of the price? Thus, you must group by those columns. Whenever you use a group function such as sum, max, min, avg . . . you'll need to group by all other non-group-function columns. How else would Oracle be able to determine the "span" or "window" across which to aggregate those values?
    Oracle DBA and Developer

  4. #4
    Join Date
    Jun 2001
    Posts
    33

    Thanks to both of you

    regards
    nath

Posting Permissions

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


Click Here to Expand Forum to Full Width