Arturo Martinez Arturo Martinez - 23 days ago 7
SQL Question

SQL query Multiple Sums and Groups

i have the Following Table that has multiple dishes in multiple quantities for different dates

[Dishes]
Date | Dish_Id | Quantity | Price | Total
============================================================================
2016-09-09 00:00:00.000 5 1 50.00 50.00
2016-09-09 00:00:00.000 3 1 30.00 30.00
2016-09-10 00:00:00.000 10 1 100.00 100.00
2016-09-09 00:00:00.000 5 1 50.00 50.00
2016-09-09 00:00:00.000 7 1 70.00 70.00
2016-09-09 00:00:00.000 7 1 70.00 70.00
2016-09-09 00:00:00.000 3 1 30.00 30.00
2016-09-10 00:00:00.000 3 1 30.00 30.00


What i want to do is a SQL query that will combine every unique/distinct Dish_Id and sum its equivalent Quantities And Total but in groups acording to the Date value without affecting its price per item
Thus if you ran the query on the table above it would produce the following result sets:

[Result]
Date | Dish_Id | Quantity | Price | Total
============================================================================
2016-09-09 00:00:00.000 5 2 50.00 100.00
2016-09-09 00:00:00.000 3 2 30.00 60.00
2016-09-09 00:00:00.000 7 2 70.00 140.00
2016-09-10 00:00:00.000 10 1 100.00 100.00
2016-09-10 00:00:00.000 3 1 30.00 30.00


im sure a GroupBy is the correct Approach but im not sure how to sum() multiple columns and just using the groupby for the "Date" Value without affecting the "Price"
Something like this maybe

select Date, Dish_Id, sum(quantity), Price, Sum(Total)
from Dishes
group by Date, Price


EDIT************
GOT IT WORKING NOW
THANKS EVERYONE

Answer

How about this?

SELECT DATE
    ,dish_id
    ,sum(quantity) AS Quantity_Total
    ,sum(price * quantity) AS TotalPrice
FROM table1
GROUP BY DATE
    ,dish_id

enter image description here

See here for online example/sandbox http://rextester.com/OLAJ52246

Comments