CODEWITHSUNDEEP
mysqlsqlselectjoinunion
Aaron
Aaron

Reputation: 25

Having Trouble Summing Two Parts of Union in MySQL

so I've got a question that shouldn't be too hard to answer, but for whatever the reason, I'm just not able to get it. I need to sum the costs of two different tables. I've tried doing one long join, but the numbers themselves come out wrong. The only way I can get the correct numbers is making two queries and summing them together. However, I want them to display under one ID.

SELECT s.storeId, s.street, s.city, s.state, s.zipcode, SUM(p.cost)
FROM store s
JOIN video v ON s.storeId=v.storeId
JOIN previousrental p ON v.videoid=p.videoid
GROUP BY s.storeId

UNION

SELECT s.storeId, s.street, s.city, s.state, s.zipcode, SUM(r.cost)
FROM store s
JOIN video v ON s.storeId=v.storeId
JOIN rental r ON v.videoid=r.videoid
GROUP BY s.storeId

Upvotes: 2

Views: 52

Answers (3)

sgeddes
sgeddes

Reputation: 62831

One option would be to put the results in a subquery: 

SELECT 
    storeId, street, city, state, zipcode, SUM(cost) 
FROM 
    (SELECT s.storeId, s.street, s.city, s.state, s.zipcode, SUM(p.cost) cost
    FROM store s
    JOIN video v ON s.storeId = v.storeId
    JOIN previousrental p ON v.videoid = p.videoid
    GROUP BY s.storeId
    UNION
    SELECT s.storeId, s.street, s.city, s.state, s.zipcode, SUM(r.cost)
    FROM store s
    JOIN video v ON s.storeId = v.storeId
    JOIN rental r ON v.videoid = r.videoid
    GROUP BY s.storeId
) T
GROUP BY storeId

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT s.storeId, s.street, s.city, s.state, s.zipcode, SUM(p.cost)
FROM store s
INNER JOIN video v ON s.storeId=v.storeId
INNER JOIN (SELECT p.videoid, SUM(p.cost) cost
                FROM previousrental p 
                GROUP BY p.videoid
                UNION 
                SELECT r.videoid, SUM(r.cost) cost
                FROM rental r
                GROUP BY r.videoid
              ) AS p ON v.videoid=p.videoid
GROUP BY s.storeId;

OR

SELECT s.storeId, s.street, s.city, s.state, s.zipcode, 
         SUM(ISNULL(p.cost, 0) + ISNULL(r.cosr, 0))
FROM store s
INNER JOIN video v ON s.storeId = v.storeId
LEFT OUTER JOIN (SELECT videoid, SUM(cost) cost FROM previousrental GROUP BY videoid) p ON v.videoid = p.videoid
LEFT OUTER JOIN (SELECT videoid, SUM(cost) cost FROM rental GROUP BY videoid) r ON v.videoid = r.videoid
GROUP BY s.storeId;

Upvotes: 1

StuartLC
StuartLC

Reputation: 107237

You can wrap the union into a derived table, and reason over that:

SELECT x.storeId, x.street, x.city, x.state, x.zipcode, SUM(x.cost)
FROM
(
    SELECT s.storeId, s.street, s.city, s.state, s.zipcode, p.cost
    FROM store s
    JOIN video v ON s.storeId=v.storeId
    JOIN previousrental p ON v.videoid=p.videoid

    UNION

    SELECT s.storeId, s.street, s.city, s.state, s.zipcode, r.cost
    FROM store s
    JOIN video v ON s.storeId=v.storeId
    JOIN rental r ON v.videoid=r.videoid
) x
GROUP BY x.storeId, x.street, x.city, x.state, x.zipcode;

Remember to group by all non-aggregate columns in the select, even if they are correlated!

Upvotes: 0

Related Questions