CODEWITHSUNDEEP
mysqlsum
Root
Root

Reputation: 2349

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

Upvotes: 3

Views: 10915

Answers (6)

Akhil
Akhil

Reputation: 2602

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

Upvotes: 5

Ravi
Ravi

Reputation: 31397

Try this

SELECT SUM(C.TOTAL) AS TOTAL_CREDIT FROM (SELECT SUM(A.price) AS TOTAL FROM TABLE1 A WHERE A.acc_id = '555'
UNION ALL
SELECT SUM(B.price) AS TOTAL FROM TABLE2 B WHERE B.account = '555' && B.active='1') C;

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Join could be better. :) Would be even better if you could have showed us the table schema. Here is a solution based on some assumed sample data.

Sample data:

Table1:

ID  PRICE
11  200
55  300
33  200
44  100
55  500

Table2:

ID  PRICE   ACTIVE
1   200     0
2   300     1
55  200     0
55  100     1
55  400     1

Query:

select sum(t.price) + x.tb2credit
from tb1 as t
inner join
(SELECT id, SUM(price) AS Tb2Credit 
FROM tb2
WHERE id = 55
and `Active`=1) x
on t.id = x.id

Results:

SUM(T.PRICE) + X.TB2CREDIT
1300

Upvotes: 0

Dave Sexton
Dave Sexton

Reputation: 11188

How about this:

SELECT SUM(a) 
FROM
  (SELECT SUM(price) AS a
   FROM TABLE1 
   WHERE acc_id = '555'
   UNION ALL
   SELECT SUM(price) AS a
   FROM TABLE2 
   WHERE account = '555' && active='1')

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

try this

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

EDIT:

here what you looking for i think

 SELECT (SUM(t1.price)+SUM(t2.price) )/2  AS TotalCredit 
FROM Table1 AS t1, Table2 AS t2 
WHERE t1.`acc_id` = '555' && t2.`account`='555' && t2.`active`='1'

DEMO FIDDLE HERE

Upvotes: 0

Jonathan de M.
Jonathan de M.

Reputation: 9808

try that

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

Upvotes: 0

Related Questions