CODEWITHSUNDEEP
sqlsql-serverselect
Mario Vega
Mario Vega

Reputation: 67

Sum of rows to columns

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

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

Now imagine records like this.

1| Luigi | Housing  | 100
1| Luigi | Housing  | 200
1| Luigi | Housing  | 150
1| Luigi | Mortage  | 60
1| Luigi | Mortage  | 30
1| Luigi | Revolving| 60
1| Luigi | Other    | 100
1| Luigi | Housing  | 100
2| Toad  | Housing  | 200
2| Toad  | Revolving| 150
2| Toad  | Revolving| 60
2| Toad  | Revolving| 30
2| Toad  | Other    | 60
2| Toad  | Other    | 100

I looking for a query that returns the sum and the count of each type of liabilities (Housing, Mortage, Revolving, Other) for each client

The output should be something like this.

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

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

id, name, h_sum, h_count, M_sum, M_count, R_sum, R_count, O_sum, O_count,

id|  name |h_sum|h_count|M_sum|M_count|R_sum|R_count|O_sum|O_count 
1 | Luigi | 550 |   4   |  90 |   2   |  60 |   1   | 100 |   1 
2 | Toad  | 100 |   1   |  0  |   0   | 240 |   3   | 160 |   2

Any clue or help is welcome, thank you in advance for your time.

Upvotes: 0

Views: 43

Answers (2)

Gaël J
Gaël J

Reputation: 15090

Do you really need this output with "hard coded" columns?

I would just use following query and interpret it later:

SELECT id, name, type, COUNT(*), SUM(amount)
FROM t
GROUP BY id, name, type

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can use conditional aggregation:

select id, name,
       sum(case when type = 'Housing' then amount end) as h_sum,
       sum(case when type = 'Housing' then 1 else 0 end) as h_count,
       sum(case when type = 'Mortage' then amount end) as m_sum,
       sum(case when type = 'Mortage' then 1 else 0 end) as m_count,
       sum(case when type = 'Revolving' then amount end) as r_sum,
       sum(case when type = 'Revolving' then 1 else 0 end) as r_count,
       sum(case when type = 'Other' then amount end) as o_sum,
       sum(case when type = 'Other' then 1 else 0 end) as o_count
from t
group by id, name;

Upvotes: 2

Related Questions