Обсуждение: 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.