Search code examples
sqlgroup-bysumdistinctansi-sql

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

Solution

  • 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