mysql calculation with sum from 2 different tables

Question

if have 2 tables:

TABLE "deposit"
card | amount
------------
2 | 123.43
2 | 56.45
3 | 21.19

+

TABLE "payment"
card | price | status
-----------
2 | 10.59 | finish
2 | 10.59 | pending
10 | 12.40 | finish
2 | 10.59 | finish

What i looking for is the remaining deposit for a card-id.

Example for card 2: 123.43 + 56.45 - 10.59 - 10.59 (only status = finish)

or: SUM(deposit for card id = 2) - SUM(payment for card id = 2 and status = finish)

I tried the following mysql-select:

SELECT(
       IFNULL(SUM(deposit.amount),0) - IFNULL(SUM(payment.price),0)
        ) AS remaing_deposit
        FROM deposit, payment
        WHERE deposit.card = '2'
        OR (
            payment.card = '2' AND payment.status = 'finish'
        )

But i get complete wrong Numbers.

Can anybody help me?

You seem to be missing some PRIMARY KEYS!

Strawberry
– Strawberry

2014-02-25 15:08:20 +00:00
Commented Feb 25, 2014 at 15:08 — Strawberry
– Strawberry, Commented Feb 25, 2014 at 15:08

Gordon Linoff · Accepted Answer · 2014-02-25 14:53:22Z

2

One challenge here is that the two tables have different cards in them. MySQL doesn't support full outer join. You can get the list of cards doing a union.

The rest of the query is solved by aggregating the two tables in the from clause an d using left outer join on allcards:

select allcards.card, coalesce(d.deposit, 0) - coalesce(p.price, 0) as RemainingDeposit
from (select card from deposit union
      select card from payment
     ) allcards left outer join
     (select card, sum(amount) as deposit
      from deposit
      group by card
     ) d 
     on d.card = allcards.card left outer join
     (select card, sum(price) as price
      from payment
      where status = 'finish'
      group by card
    ) p
    on p.card = allcards.card;

answered Feb 25, 2014 at 14:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

mdunisch Over a year ago

Thank you for you quick help! It works perfect!!!! i add "WHERE card = 2" to get the remainingDeposit for one card.

Collectives™ on Stack Overflow

mysql calculation with sum from 2 different tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related