Finding percentage between columns in Oracle

Question

I'm about one click from getting this done and so any help would be great I have this sql code I wrote in Oracle:

   SELECT DISTINCT 
          O.shipcountry, S.companyname,
          O.orderid,
          TO_CHAR(O.freight, '$999,999.999'), 
          TO_CHAR(sum(unitprice)*count(quantity), '$999,999.99') as "Order Total"
     FROM corp.Order_Details D
LEFT JOIN corp.Orders O ON D.orderid = O.orderid)
LEFT JOIN corp.Shippers S ON O.shipvia = S.Shipperid
    WHERE O.freight >= '500'
 GROUP BY O.shipcountry, S.companyname, O.orderid, to_char(O.freight, '$999,999.999');

This basically gives me an orderid, country it was shipped to, shipper name, order total and the total amount of freight that was charged.

Question

Without making this too complicated, does anyone know how I could write in the percent of freight (freight/order total)? I tried to put it in but it says that it cannot be grouped because of the sum in the order total. If anyone has any ideas on how and where I should write this line I would really, really appreciate.

Answer 1

So...

SELECT DISTINCT 
       O.shipcountry, 
       S.companyname, 
       O.orderid,
       TO_CHAR(O.freight, '$999,999.999'), 
       TO_CHAR(SUM(unitprice) * COUNT(quantity), '$999,999.99') as "Order Total", 
       O.freight / (SUM(unitprice) * COUNT(quantity)) as "Percent"
   ...

...doesn't work?

Answer 2

I assume you mean that you added something like O.freight / (SUM(unitprice) * COUNT(quantity)) and got ORA-00979: not a GROUP BY expression as a result.

The reason for this is that O.freight is not one of your GROUP BY expressions -- you are grouping by to_char(O.freight, '$999,999.999'). You should be able to group by the column value alone and have it work.

  SELECT DISTINCT 
          O.shipcountry, S.companyname,
          O.orderid,
          TO_CHAR(O.freight, '$999,999.999'), 
          TO_CHAR(sum(unitprice)*count(quantity), '$999,999.99') as "Order Total",
          O.freight / (SUM(unitprice) * COUNT(quantity)) as "Percent"
     FROM corp.Order_Details D
LEFT JOIN corp.Orders O ON D.orderid = O.orderid)
LEFT JOIN corp.Shippers S ON O.shipvia = S.Shipperid
    WHERE O.freight >= '500'
 GROUP BY O.shipcountry, S.companyname, O.orderid, O.freight;

I suspect it is a common misconception that the expressions in the GROUP BY clause must exactly match the ungrouped expressions in the SELECT clause. It is sufficient to group by the actual values -- you can still apply functions to them in the selection.