CODEWITHSUNDEEP
sqloracle-database
Jen
Jen

Reputation: 29

Finding percentage between columns in Oracle

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.

Upvotes: 2

Views: 2288

Answers (2)

Dave Costa
Dave Costa

Reputation: 48121

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.

Upvotes: 0

Will A
Will A

Reputation: 24988

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?

Upvotes: 1

Related Questions