CODEWITHSUNDEEP
sqloracle-databasegroup-by
aofe1337
aofe1337

Reputation: 67

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 & Cost
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

Upvotes: 0

Views: 51

Answers (1)

sstan
sstan

Reputation: 36483

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

Upvotes: 2

Related Questions