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...

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.

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.

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.

all "columns" other than the column involved with SUM() need to be included in the GROUP BY

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";

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.