Re: Get multiple columns with counts from one table.

On Wed, Jun 12, 2013 at 3:09 PM, chuydb <jdelbosque(at)cic(dot)mx> wrote:
> Im trying to get something like this: (one column with my types grouped
> together and multiple columns with the count vales for each place)
> I get:
> | type | home | school | work | cafe | friends | mall |
> -------------------------------------------------------------------------------------------------------------------
> | one | 2 | | 2 | | | |
> | two | | 1 | | 1 | | |
> | three | | | 2 | | | |
> | four | | | | 1 | | |
> | five | | | | 1 | 1 | |
> | six | | | | | | 1 |

First you need to get the counts by grouping by type and place
together, and then crosstab(text source_sql, text category_sql) from
the tablefunc module will help you to get this.

http://www.postgresql.org/docs/9.2/static/tablefunc.html#AEN144882

It is documented pretty good and has a lot of useful examples.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com