with a table like below
+------+-----+------+----------+-----------+
| city | day | hour | car_name | car_count |
+------+-----+------+----------+-----------+
| 1 | 12 | 00 | corolla | 8 |
| 1 | 12 | 00 | city | 9 |
| 1 | 12 | 00 | amaze | 3 |
| 1 | 13 | 00 | corolla | 17 |
| 1 | 13 | 00 | city | 2 |
| 1 | 13 | 00 | amaze | 8 |
| 1 | 14 | 00 | corolla | 3 |
| 1 | 14 | 00 | amaze | 1 |
+------+-----+------+----------+-----------+
need to find out the city, day, hour where the car_count for all car_names is >= 3 and <= 10
expected result
| city | day | hour |
+------+-----+------+
| 1 | 12 | 00 |
Use group by and having.
select city,day,hour
from tablename
group by city,day,hour
having sum(case when car_count>=3 and car_count<=10 then 1 else 0 end) = count(*)
You can group by on city, day and hour with the having condition sum(your condition) = count(your condition)
So basically we are creating a flag for each row which satisfies the condition "10 >= car_count >= 3" . Now we are summing all the flags and counting them simultaneously, if both the count and sum are equal that means your condition "10 >= car_count >= 3" was true for all the cars against city,day and hour
create table want as
select city,day,hour from have
group by city,day,hour
having sum(car_count>=3 and car_count<=10)=count(car_count>=3 and car_count<=10);
Please let me know in case of any queries.
select city, day, hour
from t
group by 1, 2, 3
having bool_and(car_count >= 3)