CODEWITHSUNDEEP
mysqlsql
Edgars Karķis
Edgars Karķis

Reputation: 83

Getting data form 2 tables and summing values of the result

I have 2 tables with information: ID, persona_id, total_amount The persona ID can repeat dozen of times. So i get all the one persons id total_amount with query:

select d.id as debt_id, p.name as persona, sum(d.total_amount) as total_amount 
from debt d 
     join persons p on d.persona_id = p.id group by p.name

I want to get data from each table in one query and do aritmethic propertys with the total_amount column and return it as 1 tabel.

TABLE 1

id persons_id total_amount
1   2         50
2   3         100
3   2         200
4   5         300
5   1         500

TABLE 2

id persons_id total_amount
1   2         25
2   1         100
3   5         50
4   3         100
5   4         300

As a result i want to get the 2 tables comined with arithmetic operation (-, +, / , * ) of Total amount columns.Basicaly a change to get the ending result total amount in form i want for different cases.

What worked for me based on JohnHC answear was :

select c.id, c.persona_id, c.total_amount - d.total_amount as new_total

from (          select c.id , c.persona_id, sum(c.total_amount) as total_amount from credit c
                join persons p on c.persona_id = p.id
                group by p.name) c
inner join (    select d.id, d.persona_id, sum(d.total_amount) as total_amount from debt d
                join persons p on d.persona_id = p.id 
                group by p.name) d

on c.persona_id = d.persona_id
group by c.id, c.persona_id

Upvotes: 1

Views: 38

Answers (1)

JohnHC
JohnHC

Reputation: 11195

If you want the total, try:

select id, person_id, sum(total_amount)
from
(
select id, person_id, total_amount
from table1
union all
select id, person_id, total_amount
from table2
)
group by id, person_id

If you want to do other things, try:

select t1.id, t1.person_id, t1.total_amount [+ - / *] t2.total_amount as new_total
from table1 t1
inner join table2 t2
on t1.id = t2.person_id
group by t1.id, t1.person_id

Upvotes: 1

Related Questions