Understanding the behavior of the GROUP BY statement

Question

The question is ..

The 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 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 it.

 +-------------------------+ | facility_map_listing_id | +-------------------------+ | 18 | | 20 | +-------------------------+ 2 rows in set (0.00 sec) 

It is right! - but can anyone explain why GROUP BY should be in the statement?

if it isnt and I run the same query, leaving out GROUP BY I get ..

 +-------------------------+ | facility_map_listing_id | +-------------------------+ | 18 | +-------------------------+ 1 row in set (0.00 sec) 

Can anyone explain this to me? Thanks!

Answer 1

Without group by collection like count works on the set as a whole. Thus, 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 a single line if the having condition is satisfied, and an empty line otherwise.

Now, with the by group, it computes the having condition for each facility_map_listing_id value. This can return up to several rows, since there are different facility_map_listing_id values.

Answer 2

I think this should explain things:

If you omit the group, all lines not excluded by the where clause return as a separate group.

So basically you are still using a group ... just the whole set.

Answer 3

we can use the HAVING clause without the GROUP BY , but SELECT columns and HAVING columns should be used with aggregate functions:

• AVG () - returns the average value
• COUNT () - returns the number of rows
• FIRST () - returns the first value
• LAST () - returns the last value
• MAX () - returns the highest value
• MIN () - returns the smallest value
• SUM () - returns the amount

HAVING is typically used with the GROUP BY and is mainly used to manage and filter data using aggregate functions. When GROUP BY is not used, HAVING behaves like a WHERE clause.

HAVING sets the conditions for the GROUP BY , similar to where the conditions for the select clause are specified.

See This and This