root root - 6 months ago 21
MySQL Question

Combine two Mysql SUM select query to one query

I have below two query's SUM the values

Query1:*


SELECT SUM(price) FROM TABLE1 WHERE
acc_id
= '555'


Query2:


SELECT SUM(price) FROM TABLE2 WHERE
account
= '555' && active='1'


I try to combine this two query but give wrong sum result , for example if
query1 sum is: -86500
and
Query2 sum is: 76000
, RESULT must be
-10500
but result shown with a number like
-486000


I'm trying like this, but i'm not getting expected result.

SELECT SUM(t1.price + t2.price) AS TotalCredit
FROM TABLE1 AS t1, TABLE2 AS t2
WHERE t1.`acc_id` = '555' && t2.`Account`='555' && t2.`Active`='1'


Table image : enter image description here

Answer

Due to join the number of records get duplicated and you get a higher value for sum try this

SELECT sum(prc) 
FROM (
    SELECT SUM(price) prc FROM TABLE1 WHERE acc_id = '555'
    union all 
    SELECT SUM(price) prc FROM TABLE2 WHERE account = '555' && active='1'
) a