Manh Le Manh Le - 9 days ago 5
SQL Question

SQL Select sum price from two tables with two conditions on one syntax

I have two tables about Customer and Transaction.
I want to get two sums of price of each customer that has at least one record in table transaction.
These results of price have different condition. But I want to show all in only one line.
How can I do?

P/s: I attached the picture of data:
enter image description here

Answer

There are a couple of ways of doing this. The most elegant, IMHO, would be a straight forward join according to the customer_id, and then summing over case expressions to handle the other conditions

SELECT   a.name, 
         SUM(CASE debit WHEN 131 THEN price) AS total_debit,
         SUM(CASE credit WHEN 131 THEN price) AS total_credit
FROM     customer a
JOIN     transactions b ON a.customer.id = b.customer_id
WHERE    131 IN (b.debit, credit)
GROUP BY a.name