SQL query to transform combination of values into a column

Question

I have a table like this:

user_id, gender, sent
1        M       100
1        F       120
2        M       20
2        F       30

I want a table like this from the above:

user_id, male_sent, female_sent, total_sent 
1        100        120          220 
2        20         30           50

I lack the (Postgres) SQL foo to figure this one out.

Have you had a look at the tag description of `psql`? You are probably talking talking about PostgreSQL. — Erwin Brandstetter, Mar 22 '13 at 03:58

score 2 · Accepted Answer · answered Mar 17 '13 at 23:11

You can use an aggregate function with a CASE expression to get the result:

select user_id,
  sum(case when gender = 'M' then sent else 0 end) male_sent,
  sum(case when gender = 'F' then sent else 0 end) female_sent,
  sum(sent) total_sent
from yourtable
group by user_id

See SQL Fiddle with Demo

score 2 · Answer 2 · answered Mar 17 '13 at 23:14

What database are you using?

If you are using SQL Server, you could do something like this:

SELECT user_id,sum(case when gender = 'M' then sent else 0 end) as male_sent,
sum(case when gender = 'F' then sent else 0 end) as female_sent,
sum(sent) as total_sent
FROM your_table_name
GROUP BY user_id

SQL query to transform combination of values into a column

2 Answers2