MySQL Summing across multiple tables

Question

I have four tables, each looks like

ID    Value
1     23.43
2     34.23
1     32.23
3     11.25

There can be multiple value entries for each ID within each table. Each ID has values in all four tables.

I want to write a query that gives me the aggregated results per ID. I want to add two of the tables' values and subtract the other two's values from the aggregate.

If were to be writing this in pseudocode:

foreach ID
    sumTable1 = sum(Table1.value) where ID=ID
    sumTable2 = sum(Table2.value) where ID=ID
    sumTable3 = sum(Table3.value) where ID=ID
    sumTable4 = sum(Table4.value) where ID=ID
    print ID . ": " . sumTable1+sumTable2-sumTable3-sumTable4

Can I do this in a single mySQL query?

Michael Berkowski · Accepted Answer · 2011-10-20 19:22:54Z

1

This is untested, but may work as expected.

SELECT 
  t1.ID,
  (SUM(t1.Value)+SUM(t2.Value)-SUM(t3.Value)-SUM(t4.Value)) AS aggregate_sum
FROM 
  t1 LEFT JOIN t2 ON t1.ID = t2.ID
  LEFT JOIN t3 ON t1.ID = t3.ID
  LEFT JOIN t4 ON t1.ID = t4.ID
GROUP BY t1.ID

EDIT Changed them all to LEFT JOIN in case the ID is absent from any table...

answered Oct 20, 2011 at 19:22

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2011-10-20 19:28:59Z

0

This should work with no assumptions about values existing in each table:

SELECT q.ID, SUM(q.Value) AS Total
    FROM (SELECT ID, Value
              FROM Table1
          UNION ALL
          SELECT ID, Value
              FROM Table2
          UNION ALL
          SELECT ID, -Value AS Value
              FROM Table3
          UNION ALL
          SELECT ID, -Value AS Value
              FROM Table4) q
     GROUP BY q.ID

answered Oct 20, 2011 at 19:28

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Collectives™ on Stack Overflow

MySQL Summing across multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related