Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Unanswered: Need some advice on a query...

    I have two orders and each order can have a few parts. I am looking for the combined commission for each order. Here is the query...

    Code:
    SELECT TRIM(c.first)||' '||c.last AS "Customer Name", o.order_number, TRIM(sr.first)||' '||sr.last AS "Customer
    Rep",TO_CHAR(ROUND((ol.number_ordered * ol.quoted_price) * sr.commission_rate,2), '$9,999.99')"Total Commission"
    	FROM customer  c, sales_rep  sr, orders o, order_line  ol
    	WHERE sr.slsrep_number = c.slsrep_number
    	AND c.customer_number = o.customer_number
    	AND o.order_number = ol.order_number
    	AND c.zip_code = 49441;
    Here is what is returned to me..
    CusName OrdNum SalesRep Comm.
    Mary Nelson 12498 Miguel Diaz $1.30
    Mary Nelson 12498 Miguel Diaz $4.99
    Mary Nelson 12504 Miguel Diaz $32.60

    This is what I am looking for...
    Mary Nelson 12498 Miguel Diaz $6.29
    Mary Nelson 12504 Miguel Diaz $32.60
    (I'm so close!)

    First thing I tried was changing the TO_CHAR(ROUND((.... to
    TO_CHAR(SUM((. Then adding a GROUP BY, but TRIM(c.first) was invalid.

    Then I tried to format the "Total Commission" in the Column and that was a total fail... So any advice would be appreciated.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Jsin View Post
    First thing I tried was changing the TO_CHAR(ROUND((.... to
    TO_CHAR(SUM((. Then adding a GROUP BY, but TRIM(c.first) was invalid.
    Yes, this is the right way. However, as you did not post the modified query and the exact error message, it is impossible to say what you did wrong.

  3. #3
    Join Date
    Jan 2012
    Posts
    16
    Sorry about that this is what I got when I tried using SUM...


    SQL> SELECT TRIM(c.first)||' '||c.last AS "Customer Name", o.order_number, TRIM(sr.first)||' '||sr.l
    ast AS "Customer Rep",TO_CHAR(SUM((ol.number_ordered * ol.quoted_price) * sr.commission_rate), '$9,9
    99.99')"Total Commission"
    2 FROM customer c, sales_rep sr, orders o, order_line ol
    3 WHERE sr.slsrep_number = c.slsrep_number
    4 AND c.customer_number = o.customer_number
    5 AND o.order_number = ol.order_number
    6 AND c.zip_code = 49441;
    SELECT TRIM(c.first)||' '||c.last AS "Customer Name", o.order_number, TRIM(sr.first)||' '||sr.last A
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

    Then I tried adding GROUP BY 2 and tried other Group BY and get this same error message.... not a GROUP BY expression.
    Thanks for the help.
    Last edited by Jsin; 02-08-12 at 18:40.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    all "columns" other than the column involved with SUM() need to be included in the GROUP BY
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2012
    Posts
    16
    Oh OK! Well I'm not to sure how the GROUP BY works so would I write it like this..

    GROUP BY c.first, c.last, o.order_number, sr.first, sr.last;

    or do I do

    GROUP BY "Customer Name", o.order_number, "Customer Rep";

  6. #6
    Join Date
    Jan 2012
    Posts
    16
    I got it to work...

    GROUP BY c.first, c.last, o.order_number, sr.first, sr.last;

    did the trick! Flyboy and anacedent thank you for your help.

Posting Permissions

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