CODEWITHSUNDEEP
mysqljoinsql-order-byunion
Bill Demaria
Bill Demaria

Reputation: 71

SQL - Order By Sum of Multiple Tables Sharing Common Field

I have 2 different tables sharing the same column names. The tables list the same products which are identified by 'id'. The products have different revenues throughout both tables and are listed multiple times in each table.

I would like to sum the revenue of the same products across the 2 tables and ORDER BY the sum. Result is sorting the highest revenue products first.

I've tried JOIN and UNION but can't seem to figure out the right solution.

UNION query I tried...

SELECT id, SUM(rev) as total 
FROM (
        SELECT id, rev FROM table1 UNION ALL
        SELECT id, rev FROM table2 UNION ALL
) 
ORDER BY total DESC

JOIN query I tried...

SELECT table1.id, 
       table1.rev, 
       table2.id,
       table2.rev, 
      (table1.rev + table2.rev) as revenue
FROM table1
INNER JOIN table2 ON table1.id = table2.id
ORDER BY revenue DESC

Upvotes: 1

Views: 1319

Answers (3)

Paul Chernoch
Paul Chernoch

Reputation: 5553

You were close. You needed:

  1. one UNION ALL, not two.

  2. a GROUP BY, that gives the break field.

  3. An alias for the subquery (I used AllRevenue - you can use any valid name.)

    SELECT id, SUM(rev) as total  
    FROM (
            SELECT id, rev FROM table1 UNION ALL
            SELECT id, rev FROM table2 
    ) AS AllRevenue  
    GROUP BY id  
    ORDER BY total DESC

The join approach would have worked if you used a FULL OUTER JOIN, because some ids may be present in one table but not the other, but that is usually less performant.

Upvotes: 1

Stefano Losi
Stefano Losi

Reputation: 729

SELECT id, SUM(rev) as total 
FROM (
        SELECT id, rev FROM table1 UNION ALL
        SELECT id, rev FROM table2 
) 
GROUP BY id
ORDER BY total DESC

Upvotes: 0

S3S
S3S

Reputation: 25122

Looks like you just need to group on the ID then... unless I'm missing something.

select d.* from (
SELECT 
    table1.id, 
    Sum(table1.rev) + Sum(table2.rev) as revenue
FROM table1
INNER JOIN table2 ON table1.id = table2.id
GROUP BY
    table1.id) d
order by d.revenue

Upvotes: 0

Related Questions