CODEWITHSUNDEEP
mysqlsqlcakephp
Subha
Subha

Reputation: 761

any one can simplify this mysql query

any one can simplify this MYSQL query

same table i want to subtract the sum of values with 1 type to another type

ex: sum( type1 ) - sum (type 2)

SELECT (SELECT sum(exp_amount) FROM `expenses` WHERE `account_id`  = 1 && `type_id` = 2)
     - (SELECT sum(exp_amount) FROM `expenses` WHERE `account_id`  = 1 && `type_id` = 1) AS balance FROM  `expenses` 
LIMIT 0 , 1

Guide me how to handle this query in Cake php Note : without using this query directly ->query (...)

Upvotes: 0

Views: 42

Answers (1)

radar
radar

Reputation: 13425

You can get the balance with single scan of the table by using case based aggregation below

SELECT Coalesce(sum(CASE WHEN type_id=2 THEN exp_amount END), 0) - Coalesce(sum(CASE WHEN type_id =1 THEN exp_amount END),0) AS balance
FROM expenses
WHERE account_id = 1
  AND type_id IN (1,2)

Upvotes: 2

Related Questions