aofe1337 aofe1337 - 6 months ago 12
SQL Question

Group By Expression query

Select Books.Category, Books.Retail,
Round(Orderitems.Quantity * (Books.Retail - Books.Cost),0) AS Category_Profit
From Books
INNER JOIN Orderitems
ON BOOKS.Retail=ORDERITEMS.Paideach
Group by Books.Category, Books.Retail


What I'm trying to do is display the category and the total amount of profit for each category. There are two tables involved

Books Table = Category & Retail
Orderitems Table= Quantity & Paid each


So e.g Category has

Sports = Football, Basketball
Food = Chicken, Beef


The result will show the retail prices of Football + Basketball in one category and in another row it will show Chicken + Beef as one

The relation between the two tables is that retail = paid each
Also trying to round off the value of Category_Profit to the nearest full value.

I'm getting an error of ORA-00979: not a GROUP BY expression on line 2

Answer

It sounds like you only need to group by category, not retail. But then you need to use the sum aggregate function for the other values:

Select Books.Category, 
       sum(Books.Retail),
       Round(sum(Orderitems.Quantity * (Books.Retail - Books.Cost)),0) AS Category_Profit
  From Books
 INNER JOIN Orderitems
    ON BOOKS.Retail=ORDERITEMS.Paideach
 Group by Books.Category