CODEWITHSUNDEEP
mysql
karlosuccess
karlosuccess

Reputation: 885

Sum of values from 2 columns from 2 tables AND sorting by a column

I have 2 tables:

Table "credits":

id | amount | type
1    8        1
2    7        2
3    2        1
4    1        1
5    5        3
6    4        2

and

Table "debits":

id | amount
1    3
1    2
3    2
4    1
5    3
5    1

I need to get the sum of all "id's" balances (credit-debit) and grouping it by "type". So far I have this:

SELECT id, SUM(amount) as balance,
FROM
(
    SELECT id, amount FROM credits
    UNION ALL
    SELECT id, -amount FROM debits
)
unified_table
GROUP BY id

But it just gives me the "id's" balances:

id | balance
1    3
2    7
3    0
4    0
5    1
6    4

Ideally, I need something like this:

type | balance
1      3
2      11
3      1

I tried to add the "type" column in the first "select" of the union, and then group by "type". But not working I think because table "debits" dont have column "type". How can I accomplish this? Thank you for your help

Upvotes: 0

Views: 121

Answers (3)

Gbemiga Ogouby
Gbemiga Ogouby

Reputation: 126

Here is my solution: 

SELECT  
    credits.`type`, 
    credits.`amount` - IFNULL(t_debit.`d_amount`, 0) AS balance 
FROM 
    credits, 
    (SELECT id, SUM(amount) AS d_amount FROM debits GROUP BY id)t_debit 
WHERE 
    credits.`id` = t_debit.`id` 
GROUP BY 
    credits.`type`;

First I select sum of amounts from debits table group by id and after I did another select query on the credits table where credit id match to debit id. I don't use UNION operator because the id's column in debits table is an foreign key.

Upvotes: 1

Marko Juvančič
Marko Juvančič

Reputation: 5890

Try this:

SELECT Type, Sum(Amount)
  FROM (
       SELECT C.Amount - ISNULL(D.Amount, 0) AS Amount, C.Type
         FROM Credits C
    LEFT JOIN (SELECT Id, Sum(Amount) 
                 FROM Debits 
               GROUP BY ID) D ON C.Id = D.Id
  ) A
 GROUP BY A.Type

Upvotes: 1

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

I think this would do it:

SELECT c.type, sum(c.amount - IFNULL(d.amount,0))
FROM credits c LEFT OUTER JOIN (SELECT id, sum(amount) FROM debits GROUP BY id) d
    ON c.id=d.id
GROUP BY c.type

The idea is to group the debits table first, and then join it with the credits table, which will result in a table that you can group by type

Upvotes: 4

Related Questions