MySQL sum of table sums with different where clauses

Question

Trying to sum multiple sums from different tables with different where conditions for each table.

Here's my 3 sums:

SELECT SUM(1column2) FROM table1 WHERE 1column1 = '0';
SELECT SUM(1column4) FROM table1 WHERE 1column3 = '0';
SELECT SUM(2column2) FROM table2 WHERE 2column1 = '0' AND 2column3 = 'w' AND 2column4 != 'f';

The result needs to be (first sum) - (second sum) - (third sum).

score 3 · Accepted Answer · answered May 10 '12 at 17:20

3

MySQL supports basic operators, so you should be able to do something as simple as:

SELECT (SELECT SUM(1column2) FROM table1 WHERE 1column1 = '0') - (SELECT SUM(1column4) FROM table1 WHERE 1column3 = '0') - (SELECT SUM(2column2) FROM table2 WHERE 2column1 = '0' AND 2column3 = 'w' AND 2column4 != 'f');

I'm sure this can be optimized, but that should give you what you need (and without better information on the relationships, optimization would be difficult).

answered May 10 '12 at 17:20

MaddHacker

1,118
10
17

Thanks for trying. Still says "no database selected". – May 10 '12 at 17:38
are all 3 tables in the same database? have you tried "use "? – MaddHacker May 10 '12 at 17:40
Jeez, that was dumb on my part. Hadn't selected a db in phpmyadmin. – May 10 '12 at 17:46

score 1 · Answer 2 · answered May 10 '12 at 17:19

1

You could just make each of those queries subqueries to an outer query that performs your desired summation:

SELECT
    (SELECT SUM(1column2) FROM table1 WHERE 1column1 = '0')
  - (SELECT SUM(1column4) FROM table1 WHERE 1column3 = '0')
  - (SELECT SUM(2column2) FROM table2 WHERE 2column1 = '0' 
      AND 2column3 = 'w' AND 2column4 != 'f');

answered May 10 '12 at 17:19

eggyal

122,705
18
212
237

Thanks for trying, but "no database selected". Man, I was hoping it was that easy. – May 10 '12 at 17:24
@user1382306: Well if you've not selected your desired database, your original `SELECT` statements won't succeed either. Specify your database name when you connect, or issue a [`USE`](http://dev.mysql.com/doc/refman/5.0/en/use.html) statement. – eggyal May 10 '12 at 17:29
Jeez, that was dumb on my part. Hadn't selected a db in phpmyadmin. – May 10 '12 at 17:46
@user1382306: Glad to hear you worked it out. FYI, you should [accept answers](http://meta.stackexchange.com/a/5235) if they solve your problem and [upvote](http://stackoverflow.com/privileges/vote-up) them if they're useful or helpful. – eggyal May 10 '12 at 17:47

MySQL sum of table sums with different where clauses

2 Answers2