Custom Group By postgresql

Question

I am running PostgreSQL. I have a table called foo.Its content are:

city|number
'oo'|12
'ss'|11
'ss'|23
'oo'|11
'pp'|21

If I run a query like select count(city) from foo group by city having number<21 I will get

city|number
'oo'|2
'ss'|2

but I want the result to consider all the possible cases of city like this:

city|number
'oo'|2
'ss'|2
'pp'|0

How should the query look like?

What do you mean by "consider all the possible cases"? 21 is not < 21, which is why pp won't show up. — fnl
– fnl, Commented Feb 11, 2012 at 18:00
yeah but the requirement is it should consider the pp as well. — goblin2986
– goblin2986, Commented Feb 11, 2012 at 18:01
yeah i know..but my requirement is as group by generate column values according to condition but i want the tables like second where it should consider the all the cases. I need know what should be query to generate the second one. — goblin2986
– goblin2986, Commented Feb 11, 2012 at 18:04
Shouldn't the result have only 1 "ss"? Like 'ss'|1?? Not to mention there is no ID in your table! — Mosty Mostacho
– Mosty Mostacho, Commented Feb 11, 2012 at 19:11

bluish · Accepted Answer · 2012-07-24 06:54:32Z

1

SELECT city, count(NULLIF(number < 21, false)) FROM cities GROUP BY city

should give you the desired result.

edited Jul 24, 2012 at 6:54

bluish

27.6k28 gold badges126 silver badges185 bronze badges

answered Feb 11, 2012 at 18:12

fnl

2,25919 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mosty Mostacho · Accepted Answer · 2012-02-11 19:26:30Z

0

This is could be solved with a left join:

select f1.city, count(f2.number) as total from foo as f1
left join foo as f2
on f1.cityId = f2.cityId and f1.number < 21
group by f1.city

Here is a working example

PS: Your example is wrong because ss should have a value of 1, not 2:

YOUR EXAMPLE         WHAT I THINK YOU REALLY WANT
city|number                   city|number
'oo'|2                        'oo'|2
'ss'|2                        'ss'|1
'pp'|0                        'pp'|0

Hope this helps.

answered Feb 11, 2012 at 19:26

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Comments

Gary - Stand with Ukraine · Accepted Answer · 2012-02-11 18:21:19Z

0

Think I misread the question first time, but try this:

select f1.city,count(*) from foo f1
where f1.city in (select f2.city from foo f2 where f2.number < 21)
group by f1.city
union 
select f3.city,0 from foo f3
where not exists (select * from foo f4 where f3.city=f4.city and f4.number < 21)
group by f3.city

The union splits the query so that the first half will identify which cities have an entry <21, and then count the number of occurrences of those cities.

The second half identifies cities which do not have an entry <21, and then counts them.

edited Feb 11, 2012 at 18:21

answered Feb 11, 2012 at 18:16

Gary - Stand with Ukraine

7,6391 gold badge33 silver badges41 bronze badges

Collectives™ on Stack Overflow

Custom Group By postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related