Postgresql Multiple counts for one table
Solution 1
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
Solution 2
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.
Solution 3
You can use filter clause as well:
SELECT
type,
sum(1) FILTER (WHERE place = 'home') AS home,
sum(1) FILTER (WHERE place = 'school') AS school,
sum(1) FILTER (WHERE place = 'work') AS work,
sum(1) FILTER (WHERE place = 'cafe') AS cafe,
sum(1) FILTER (WHERE place = 'friends') AS friends,
sum(1) FILTER (WHERE place = 'mall') AS mall
FROM
reports
GROUP BY
type
Admin
Updated on July 25, 2022Comments
-
Admin almost 2 years
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.
-
Craig Ringer almost 11 yearsTo get blank (null) columns instead of zero, wrap the sums in
nullif
calls. Usingcount
withnull
instead of0
(by omitting theELSE 0
entirely) for the failed case works fine too, you don't have to change tosum
. -
mosid about 10 yearsThe last comma in the sql statement (right before the "from" keyword) causes an error.
-
S T Mohammed over 6 yearshow to get records for where home,school,work,cafe,friends,mall values >0
-
pratik garg over 6 yearsapologies for this late reply. By doing small change in query you can get output as what you want.