Menu
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 rightmost 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 like the one above 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.
895
To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
In PostgreSQL, the DISTINCT clause can be used with the COUNT() function to count only unique/distinct values of a table. The simple COUNT() function retrieves all the rows/records, including duplicate values and null. To count only unique rows, you must specify a DISTINCT clause with the COUNT() function.
1) COUNT(*) You can use the PostgreSQL COUNT(*) function along with a SELECT statement to return the total number of rows in a table including the NULL values as well as the duplicates.
You can count multiple COUNT() for multiple conditions in a single query using GROUP BY. SELECT yourColumnName,COUNT(*) from yourTableName group by yourColumnName; To understand the above syntax, let us first create a table. The query to create a table is as follows.
you can use case in this case -
SELECT type, sum(case when place = 'home' then 1 else 0 end) as Home, sum(case when place = 'school' then 1 else 0 end) as school, sum(case when place = 'work' then 1 else 0 end) as work, sum(case when place = 'cafe' then 1 else 0 end) as cafe, sum(case when place = 'friends' then 1 else 0 end) as friends, sum(case when place = 'mall' then 1 else 0 end) as mall from reports group by type It should solve your problem
@S T Mohammed, To get such type we can simply use using after group or where condition in outer query, as below -
select type, Home, school, work, cafe, friends, mall from ( SELECT type, sum(case when place = 'home' then 1 else 0 end) as Home, sum(case when place = 'school' then 1 else 0 end) as school, sum(case when place = 'work' then 1 else 0 end) as work, sum(case when place = 'cafe' then 1 else 0 end) as cafe, sum(case when place = 'friends' then 1 else 0 end) as friends, sum(case when place = 'mall' then 1 else 0 end) as mall from reports group by type ) where home >0 and School >0 and Work >0 and cafe>0 and friends>0 and mall>0 Answer by praktik garg is correct, it is not necessary to use else 0:
SELECT type, sum(case when place = 'home' then 1 end) as home, sum(case when place = 'school' then 1 end) as school, sum(case when place = 'work' then 1 end) as work, sum(case when place = 'cafe' then 1 end) as cafe, sum(case when place = 'friends' then 1 end) as friends, sum(case when place = 'mall' then 1 end) as mall FROM reports GROUP BY type You can also use the following even shorter syntax:
SELECT type, sum((place = 'home')::int) as home, sum((place = 'school')::int) as school, sum((place = 'work' )::int) as work, sum((place = 'cafe' )::int) as cafe, sum((place = 'friends')::int) as friends, sum((place = 'mall')::int) as mall FROM reports GROUP BY type This will work because boolean true is cast to 1 when condition is met.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
