CODEWITHSUNDEEP
mysqlsumwhere-clausemultiple-tables
user1382306
user1382306

Reputation:

MySQL sum of table sums with different where clauses

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).

Upvotes: 0

Views: 1586

Answers (2)

MaddHacker
MaddHacker

Reputation: 1128

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).

Upvotes: 3

eggyal
eggyal

Reputation: 125865

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');

Upvotes: 1

Related Questions