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
– Erwin Brandstetter, Commented Mar 22, 2013 at 3:58

Taryn · Accepted Answer · 2013-03-17 23:11:31Z

2

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

answered Mar 17, 2013 at 23:11

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard · Accepted Answer · 2013-03-17 23:14:28Z

2

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

answered Mar 17, 2013 at 23:14

Richard

4556 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL query to transform combination of values into a column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related