CODEWITHSUNDEEP
sqlsql-serverselect
Mario Vega
Mario Vega

Reputation: 67

Add a flag depending whether a value in a row exists

I have a table (tbl_liabilities) with 5 columns like this:

+------------+------------+-------+------------+------------+   
| User_id    | Name       | Type  |  Amount    | Delinquency|
+------------+------------+-------+------------+------------+

Now imagine records like this.

1| Luigi | Housing  | 100 | 1 Month
1| Luigi | Housing  | 200 | Null
1| Luigi | Housing  | 150 | 1 Month
1| Luigi | Mortage  | 60  | NULL
1| Luigi | Mortage  | 30  | NULL
1| Luigi | Revolving| 60  | 2 Months
1| Luigi | Other    | 100 | NULL
1| Luigi | Housing  | 100 | NULL
2| Toad  | Housing  | 200 | NULL
2| Toad  | Revolving| 150 | 1 Months
2| Toad  | Revolving| 60  | NULL
2| Toad  | Revolving| 30  | NULL
2| Toad  | Other    | 60  | NULL
2| Toad  | Other    | 100 | NULL

I looking for a query that returns the sum and the count of each type of liabilities (Housing, Mortage, Revolving, Other) for each client, and whether they had a delinquency in that specific liability or not.

The output should be something like this.

+------------+------------+...|---------------+-----------------------------+  
| User_id    | Name       |...|(Liability)Sum |  (Liability)DelinquencyFlag |
+------------+------------+...+---------------+-----------------------------+

For example for the aforementioned table, the result would be.

id, name, h_sum, H_flag, M_sum, M_flag, R_sum, R_flag, O_sum, O_flag,

id|  name |h_sum|H_flag|M_sum|M_flag|R_sum|R_flag|O_sum|O_flag 
1 | Luigi | 550 | YES  |  90 |  NO  |  60 |  YES | 100 |  NO 
2 | Toad  | 100 | NO   |  0  |  NO  | 240 |  YES | 160 |  NO

What I do for gathering the Liability sum is the following, for each type.

isnull(SUM(CASE WHEN Type = 'H' THEN Amount END), 0) AS H_SUM

but I'm having trouble implementing the delinquency flag. Any clue is welcome, thank you in advance for your help.

Upvotes: 0

Views: 638

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use conditional aggregation. Instead of SUM(), try MAX():

select id, name,
       sum(case when type = 'Housing' then amount else 0 end) as h_amount,
       max(case when type = 'Housing' and Delinquency is not null then 'Yes' else 'No' end) as h_delinquency
from liabilities l
group by id, name

Upvotes: 2

Related Questions