CODEWITHSUNDEEP
sqlsql-servert-sql
Hossein
Hossein

Reputation: 4137

How to pass different values of a certain column to an aggregate function conditionally?

Well the title of this question might not actually reflect what I want, I couldn't think of anything better. Maybe I should've appended a 'and then join two tables acquired this way' to the end.

Let's say I have this table. The scenario is, we pay loans with id loan_id, and we store payments of that in the table "Payment", each payment has its own id. When customer pays the payment, we change paid to 1.

+---------+----+--------+------+
| loan_id | id | amount | paid |
+---------+----+--------+------+
|       1 |  1 |   1000 |    1 |
|       1 |  2 |   1000 |    1 |
|       1 |  3 |   1000 |    0 |
|       2 |  4 |  10000 |    0 |
|       3 |  5 |  20000 |    1 |
+---------+----+--------+------+

And now we need a report on loans. The output should look like this:

+---------+-------+-----------+
| loan_id |  paid | remaining |
+---------+-------+-----------+
|       1 |  2000 |      1000 |
|       2 |     0 |     10000 |
|       3 | 20000 |         0 |
+---------+-------+-----------+

The paid column is basically sum of all amount fields where pay = 1. And the remaining is sum of the amount rows for which pay = 0. They should be grouped by loan_id.

What I tried:

I tried different joins, products, etc but the best thing I could get out of my queries was this one:

SELECT loan_id, SUM(paid), SUM(remaining)
FROM (
    SELECT loan_id, 0 AS paid, SUM(amount) AS remaining
    FROM Payment
    WHERE paid = 0
    GROUP BY loan_id

    UNION

    SELECT loan_id, SUM(amount) AS paid, 0 AS remaining
    FROM Payment
    WHERE paid = 1
    GROUP BY loan_id
)
GROUP BY loan_id

But I guess there should be better ways than mine.

Upvotes: 3

Views: 511

Answers (2)

TechDo
TechDo

Reputation: 18629

How about this:

SELECT Loan_ID,
SUM(Amount*Paid) Paid,
SUM(Amount*(1-Paid)) Remaining
FROM Payment
GROUP BY Loan_ID

Upvotes: 2

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

SELECT 
loan_id, 
SUM(case when paid = 1 then amount else 0 end) as payed, 
SUM(case when paid = 0 then amount else 0 end) as remaining
FROM Payment
GROUP BY loan_id

I would avoid to use a column name as an alias (paid => payed), for code clarity

Upvotes: 4

Related Questions