aofe1337 - 9 months ago 40

SQL Question

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