CODEWITHSUNDEEP
sqlcountsum
Feisser
Feisser

Reputation: 15

SQL Count then Sum?

Select Bill.BillId, Count(Menu.MenuId) * Sum(ShoppingCart.Number) As 'Sum'
From ShoppingCart
Left Join Bill On Bill.BillId = ShoppingCart.Bill
Right Join Menu On Menu.MenuId = ShoppingCart.Menu
Group By Bill.BillId, Menu.MenuId, ShoppingCart.Number

Result:

Billid:    Sum:
1          1
1          1
2          2
2          1
3          3
3          5

Hey guys, I'm trying to count how many dishes have been ordered but the problem is that the different dishes dont Sum together.

For explanation: In BillId you can order different dishes, in MenuId you can find the dish an in number you can find the number of orders of a dish.

Thank you in advance.

Upvotes: 1

Views: 86

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Is this what you want?

select sc.BillId, m.DishId, count(*) as dishes
from ShoppingCart sc join
     Menu m
     on m.MenuId = sc.Menu
group by sc.BillId, m.DishId;

You should not need outer joins if your data model is set up correctly.

Upvotes: 1

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

Only use the columns which are not aggregated in group by statement, so the group by statement should contain only BillID. If you include MenuId and ShoppingCart.Number then the individual values in those columns will also get group by instead of getting the aggregated value.

Select Bill.BillId, Count(Menu.MenuId) * Sum(ShoppingCart.Number) As 'Sum'
From ShoppingCart
Left Join Bill On Bill.BillId = ShoppingCart.Bill
Right Join Menu On Menu.MenuId = ShoppingCart.Menu
Group By Bill.BillId

Upvotes: 0

Related Questions