Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many messages of each type a user have send.

e.g. if I have the following table:

---------------------------
id | user_id | message_type
---------------------------
1  | 1       | private
2  | 1       | public
3  | 1       | private
---------------------------

Then I want to get the following:

---------------------
id | private | public
---------------------
1  | 2       | 1
---------------------

So in fact I want to group by message_type and user_id, but instead of generating multiple rows per user, I want to create multiple columns, one for each message_type

Can I achieve this without hardcoding the message types in my query?

share|improve this question

2 Answers

up vote 2 down vote accepted

If you have a limited number of values that you want to convert into columns, then this can easily be implemented using an aggregate function with a CASE expression:

select user_id,
  sum(case when message_type = 'private' then 1 else 0 end) private,
  sum(case when message_type = 'public' then 1 else 0 end) public
from yourtable
group by user_id

See SQL Fiddle with Demo

PostgreSQL has the ability to use a crosstab() that can be used by installing the tablefunc module. This will perform a similar data transformation from rows into columns. Creating a dynamic version of the query is not a straight forward process, here is a great solution for a dynamic crosstab on StackOverflow.

share|improve this answer
Unfortunately I can't install the tablefunc module, but your other solution works for me. Thanks! – Tiddo Mar 28 at 19:13
up vote 2 down vote

PostgreSQL doesn't directly support PIVOT, which is the keyword usually used on other platforms for something like this. It does have some crosstab functions in the tablefunc module.

Using the crosstab() function, (an excellent StackOverflow answer)

share|improve this answer
Thanks for your answer! Unfortunately I can't install the tablefunc module and thus I had to go with the solution provided by bluefeet. – Tiddo Mar 28 at 19:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.