Understanding the GROUP BY statement's behaviour
group-by, having-clause, mysql, sql
Solution
Without a `group by`, an aggregate like `count` works on the set as a whole. So this query returns either zero or one row:
SELECT facility_map_listing_id
FROM facility_map
WHERE facility_map_facility_id IN(1, 2)
HAVING count(DISTINCT facility_map_facility_id) >= 2
It will return one row if the `having` condition is met, and an empty set otherwise.
Now, with the group by, it evaluates the `having` condition for each value of `facility_map_listing_id`. That can return up to as many rows as there are distinct values of `facility_map_listing_id`.
Problem
The question is this.. Table is this.. ``` +--------------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+---------+------+-----+---------+----------------+ | facility_map_id | int(10) | NO | PRI | NULL | auto_increment | | facility_map_facility_id | int(10) | NO | MUL | NULL | | | facility_map_listing_id | int(10) | NO | | NULL | | +--------------------------+---------+------+-----+---------+----------------+ ``` Data is this.. ``` +-----------------+--------------------------+-------------------------+ | facility_map_id | facility_map_facility_id | facility_map_listing_id | +-----------------+--------------------------+-------------------------+ | 248 | 1 | 18 | | 259 | 1 | 19 | | 206 | 1 | 20 | | 244 | 1 | 21 | | 249 | 2 | 18 | | 207 | 2 | 20 | | 208 | 3 | 20 | | 245 | 3 | 21 | | 260 | 4 | 19 | | 261 | 5 | 19 | | 246 | 6 | 21 | | 250 | 7 | 18 | | 247 | 8 | 21 | +-----------------+--------------------------+-------------------------+ ``` I run the this query : ``` SELECT facility_map_listing_id FROM facility_map WHERE facility_map_facility_id IN(1, 2) GROUP BY facility_map_listing_id HAVING count(DISTINCT facility_map_facility_id) >= 2 ``` and get this.. ``` +-------------------------+ | facility_map_listing_id | +-------------------------+ | 18 | | 20 | +-------------------------+ 2 rows in set (0.00 sec) ``` Which is correct! - but can anyone explain, why the GROUP BY needs to be in the statement? if it Isnt and I run the same query leaving out the GROUP BY I get.. ``` +-------------------------+ | facility_map_listing_id | +-------------------------+ | 18 | +-------------------------+ 1 row in set (0.00 sec) ``` Can any one explain this to me? Thank you!