Re: Get multiple columns with counts from one table.

On Wed, Jun 12, 2013 at 3:09 PM, chuydb <jdelbosque@cic.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.ru@gmail.com