Sum values from multiple tables grouping by a common column

Question

I have three tables in MS SQL Server 2014. Each of them holds a couple of numeric values, a description and a date. For the sake of brevety, let's assume the following tables:

table "beverages"

day        beverage amount
---------- -------- ------
2018-12-01 water    2
2018-12-01 tea      1
2018-12-01 coffee   7
2018-12-02 water    4
2018-12-02 tea      2


table "meals"

day        meal   amount
---------- ------ ------
2018-12-01 burger 1
2018-12-01 bread  2
2018-12-02 steak  1


table "fruit"

day        fruit  amount
---------- ------ ------
2018-12-01 apple  4
2018-12-01 banana 1
2018-12-02 apple  2

Then I have another table holding only a list of dates.

table "dates"

day
----------
2018-12-01
2018-12-02

What I need is a query that returns one row for each of the rows in the dates table, and in each row has the date, the total amount of beverages, the total amount of meals and the total amount of fruit for that day. I do not care for the different types of beverages, meals and fruit, just the sum. The result should be:

expected result

day        beverages   meals       fruit
---------- ----------- ----------- -----------
2018-12-01 10          3           5
2018-12-02 6           1           2

But instead I receive

received result

day        beverages   meals       fruit
---------- ----------- ----------- -----------
2018-12-01 40          18          30
2018-12-02 6           2           4

I already know what the problem is, just not how to fix it. Even worse, I'm sure that I knew the answer once, but now I can't even figure the right search terms to make Google tell me...

When I do the query like this (I used table variables for testing)

SELECT 
     [d].[day]
    ,SUM([b].[amount]) AS [beverages]
    ,SUM([m].[amount]) AS [meals]
    ,SUM([f].[amount]) AS [fruit]
FROM @dates AS [d]
LEFT OUTER JOIN @beverages AS [b]
    ON [d].[day] = [b].[day]
LEFT OUTER JOIN @meals AS [m]
    ON [d].[day] = [m].[day]
LEFT OUTER JOIN @fruit AS [f]
    ON [d].[day] = [f].[day]
GROUP BY [d].[day]

it sums each row from the different tables more than once, because it returns every possible combination of the three tables. Removing the SUM() and GROUP BY proves that:

day        beverages   meals       fruit
---------- ----------- ----------- -----------
2018-12-01 2           1           4
2018-12-01 2           1           1
2018-12-01 2           2           4
2018-12-01 2           2           1
2018-12-01 1           1           4
2018-12-01 1           1           1
2018-12-01 1           2           4
2018-12-01 1           2           1
2018-12-01 7           1           4
2018-12-01 7           1           1
2018-12-01 7           2           4
2018-12-01 7           2           1
2018-12-02 4           1           2
2018-12-02 2           1           2

So, what do I need to change in the query to make it sum the values for each of the three tables without multiplying it with the number of the rows in the other tables?

score 2 · Accepted Answer · answered Dec 12 '18 at 23:30

Group the Tables before joining like so:

SELECT 
     [d].[day]
    ,[b].[amount] AS [beverages]
    ,[m].[amount] AS [meals]
    ,[f].[amount] AS [fruit]
FROM @dates AS [d]
LEFT OUTER JOIN (SELECT day, SUM(amount) as amount FROM @beverages GROUP BY day) AS [b]
    ON [d].[day] = [b].[day]
LEFT OUTER JOIN (SELECT day, SUM(amount) as amount FROM @meals GROUP BY day) AS [m]
    ON [d].[day] = [m].[day]
LEFT OUTER JOIN (SELECT day, SUM(amount) as amount FROM @fruit GROUP BY day) AS [f]
    ON [d].[day] = [f].[day]

Thanks... Now that I read your answer I wonder how I could possibly have forgotten about that. You just saved my day! — Patrick Echterbruch, Dec 12 '18 at 23:36

score 1 · Answer 2 · answered Dec 12 '18 at 23:24

1

How about a PIVOT instead?

Example

Select *
 From (
        Select day,Item='beverage',amount from beverages
        Union All
        Select day,Item='meals'   ,amount from meals
        Union All
        Select day,Item='fruit'   ,amount from fruit
      ) src
Pivot ( sum(amount) for Item in ([beverages],[meals],[fruit]) ) pvt

answered Dec 12 '18 at 23:24

John Cappelletti

79,615
7
44
66

Haven't used PIVOT queries yet, so I went for the "group before joining" solution by Richard, but this is definitely something I should get used to because it appears to be a lot more flexible. Thanks for answering! – Patrick Echterbruch Dec 12 '18 at 23:40
@PatrickEchterbruch I'm sure you'll find that PIVOT will come in handy one day :) – John Cappelletti Dec 12 '18 at 23:42

Sum values from multiple tables grouping by a common column

2 Answers2