CODEWITHSUNDEEP
mysqlsqlselectsum
Relm
Relm

Reputation: 8241

MySQL multiples SUM() functions inside one query as fields

I'm trying to get each of the following sums as fields, I'm only getting the outer one, the other two return undefined index.

How do I put them outside as fields?

SELECT 
(SELECT SUM(amount) AS income
    FROM transaction 
    WHERE 
    type = '1' && state = 'a') 

- (SELECT SUM(amount) AS expense 
    FROM transaction 
    WHERE 
    type = '2' && state = 'a') 
    AS balance

Balance works, but for income and expense I get undefined index.

Upvotes: 0

Views: 71

Answers (3)

potashin
potashin

Reputation: 44581

You can use case and sum to get each result:

select sum(case when `type` = '1' then `amount` else 0 end) as `income`
     , sum(case when `type` = '2' then `amount` else 0 end) as `expense `
     , sum((case when `type` = '2' then -1 else 1 end) * `amount`) as `difference`
from `transaction`
where `type` in ('1', '2')
where `state` = 'a'

Upvotes: 1

Mureinik
Mureinik

Reputation: 310993

You cannot access income and expense since they are only defined in their respective subqueries and not in the top level query, which only has one field - the difference between them. I'd put in a subquery, and then select them both and the difference between them. To make this query easier and remove the need for subqueries, you can emulate the behavior of two different where clauses by extracting the different conditions to case expressions in the sum's arguments:

SELECT income, expense, income - expense AS balance
FROM   (SELECT SUM(CASE type WHEN 1 THEN amount ELSE NULL) AS income,
               SUM(CASE type WHEN 2 THEN amount ELSE NULL) AS expense,
        FROM   `transaction`
        WHERE  state = 'a') t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use conditional aggregation:

SELECT SUM(CASE WHEN type = '1' AND state = 'a' THEN amount ELSE 0
           END) as income,
       SUM(CASE WHEN type = '2' AND state = 'a' THEN amount ELSE 0
           END) as balance
FROM transaction;

If you still want the difference, then you need to repeat the logic:

SELECT SUM(CASE WHEN type = '1' AND state = 'a' THEN amount ELSE 0
           END) as income,
       SUM(CASE WHEN type = '2' AND state = 'a' THEN amount ELSE 0
           END) as expense,
       (SUM(CASE WHEN type = '1' AND state = 'a' THEN amount ELSE 0 END) -
        SUM(CASE WHEN type = '2' AND state = 'a' THEN amount ELSE 0 END) 
       ) as balance
FROM transaction;

EDIT:

Actually, it is better to factor out the common conditions into a WHERE clause:

SELECT SUM(CASE WHEN type = '1' THEN amount ELSE 0 END) as income,
       SUM(CASE WHEN type = '2' THEN amount ELSE 0 END) as expense,
       (SUM(CASE WHEN type = '1' THEN amount ELSE 0 END) -
        SUM(CASE WHEN type = '2' THEN amount ELSE 0 END) 
       ) as balance
FROM transaction
WHERE state = 'a' AND type IN ('1', '2');

This can take advantage of an index on transaction(state, type, amount).

Upvotes: 1

Related Questions