Thread: Get multiple columns with counts from one table.
Hi, From two columns in my table I want to get a unified count for the values in these columns. As an example, two columns are: Table: reports | type | place | ----------------------------------------- | one | home | | two | school | | three | work | | four | cafe | | five | friends | | six | mall | | one | work | | one | work | | three | work | | two | cafe | | five | cafe | | one | home | If I do: SELECT type, count(*) from reports group by type I get: | type | count | ---------------------------------- | one | 4 | | two | 2 | | three | 2 | | four | 1 | | five | 2 | | six | 1 | 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 | which would be the result of running a count for every place like this: SELECT type, count(*) from reports where place = 'home' group by type SELECT type, count(*) from reports where place = 'school' group by type SELECT type, count(*) from reports where place = 'work' group by type SELECT type, count(*) from reports where place = 'cafe' group by type SELECT type, count(*) from reports where place = 'friends' group by type SELECT type, count(*) from reports where place = 'mall' group by type Is this possible with postgresql??? Thanks in advance. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Get-multiple-columns-with-counts-from-one-table-tp5758977.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Jun 13, 2013, at 24:09, chuydb <jdelbosque@cic.mx> wrote:
> Hi,
> From two columns in my table I want to get a unified count for the values in
> these columns.
> As an example, two columns are:
>
> Table: reports
> | type | place |
> -----------------------------------------
> | one | home |
> | two | school |
> | three | work |
> | four | cafe |
> | five | friends |
> | six | mall |
> | one | work |
> | one | work |
> | three | work |
> | two | cafe |
> | five | cafe |
> | one | home |
>
> If I do:
> SELECT type, count(*) from reports
> group by type
>
> I get:
> | type | count |
> ----------------------------------
> | one | 4 |
> | two | 2 |
> | three | 2 |
> | four | 1 |
> | five | 2 |
> | six | 1 |
>
> 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 |
>
> which would be the result of running a count for every place like this:
> SELECT type, count(*) from reports where place = 'home'
> group by type
> SELECT type, count(*) from reports where place = 'school'
> group by type
> SELECT type, count(*) from reports where place = 'work'
> group by type
> SELECT type, count(*) from reports where place = 'cafe'
> group by type
> SELECT type, count(*) from reports where place = 'friends'
> group by type
> SELECT type, count(*) from reports where place = 'mall'
> group by type
>
> Is this possible with postgresql???
You can do that like this:
SELECT type, SUM(CASE place WHEN 'home' THEN 1 ELSE 0 END),
SUM(CASE place WHEN 'school' THEN 1 ELSE 0 END),
etc.
FROM reports
WHERE place IN ('home', 'school', etc.)
GROUP BY type
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
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
Thanks for your quick answer...! Ill try this out to see if I can get it working. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Get-multiple-columns-with-counts-from-one-table-tp5758977p5758981.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.