Combine two Mysql SUM select query to one query

Question

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

You have said nothing about the relation of one table to the other to know how (or if) they can be joined in a meaningful way. Can you give examples of the data you are trying to sum and talk about why the data is spread across two tables? — Mike Brant
– Mike Brant, Commented Dec 24, 2012 at 16:53
@root try my solution, i'm expecting, you would get your expected result. But, still i'm waiting for your response. — Ravi
– Ravi, Commented Dec 24, 2012 at 17:01

Fabian N. · Accepted Answer · 2016-05-26 14:52:20Z

5

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

edited May 26, 2016 at 14:52

Fabian N.

3,8682 gold badges27 silver badges47 bronze badges

answered Dec 24, 2012 at 16:57

Akhil

2,60225 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Root Over a year ago

Thanks but Have this error : Error Code: 1248 \n Every derived table must have its own alias

Akhil Over a year ago

Did you notice the a after the inner query?

Root Over a year ago

:O Thanks man , sorry I left that char ... now work on sqlfiddle.com/#!2/24c89 ... wait to test on my real table too.

chikitin Over a year ago

Would you please explain what the letter "a" is doing?

Akhil Over a year ago

@chikitin "Every derived tables in Mysql MUST have an alias".

Ravi · Accepted Answer · 2012-12-24 17:33:04Z

1

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;

edited Dec 24, 2012 at 17:33

answered Dec 24, 2012 at 16:56

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

4 Comments

Root Over a year ago

Thanks but get this error Error Code: 1248 \n Every derived table must have its own alias ... I add a image of my two table

Ravi Over a year ago

@root try now. I have updated my query. If it is helpful. Then accept it otherwise, let me know.

Root Over a year ago

thanks but give error : Unknown column 'A.price' in 'field list' , I Accept akhil answer , Thanks all ... That's a horrible problem :-"

Ravi Over a year ago

doesn't matter. you would have done something wrong,because i verified the above query. Nevertheless. You got the solution. I'm happy :)

Jonathan de M. · Accepted Answer · 2012-12-24 16:56:52Z

0

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'

answered Dec 24, 2012 at 16:56

Jonathan de M.

9,8488 gold badges50 silver badges72 bronze badges

Comments

echo_Me · Accepted Answer · 2012-12-24 17:06:02Z

0

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

edited Dec 24, 2012 at 17:06

answered Dec 24, 2012 at 16:49

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

3 Comments

Root Over a year ago

Thanks goodmood , but now give result as array , how to have a one result as SUM ?

echo_Me Over a year ago

look now my edit and my demo , try to change values in demo and see the result

Root Over a year ago

Thank too :-" but give back wrong result : sqlfiddle.com/#!2/c64ff , test it with changing just one of numbers .

Dave Sexton · Accepted Answer · 2012-12-24 17:06:34Z

0

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

answered Dec 24, 2012 at 17:06

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

1 Comment

Root Over a year ago

Thanks but get this error : Error Code: 1248 \n Every derived table must have its own alias on SQLYOG

bonCodigo · Accepted Answer · 2012-12-24 17:09:55Z

0

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:

Table2:

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

SQLFIDDLE DEMO

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

answered Dec 24, 2012 at 17:09

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

3 Comments

bonCodigo Over a year ago

@root please take a look at the solution here. Please show us your table schema :)

Root Over a year ago

acc_id from TABLE1 and account from TABLE2 is must be exact , I've added an image of my tables to first post , review it .

bonCodigo Over a year ago

@root all you need to do is, sum table 2 prices for the given acct id if where active = 1. Then add that up to table 1 sum for the same acct id. That's the logic behind the query I gave. I'll try to use your exact information :)

Collectives™ on Stack Overflow

Combine two Mysql SUM select query to one query

6 Answers 6

5 Comments

4 Comments

Comments

3 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

4 Comments

Comments

3 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related