Oracle 11g: The difference between WHERE and HAVING

SQL> select * from sys. SHIP_CABINS;
 
SHIP_CABIN_ID SHIP_ID ROOM _ ROOM_STYLE ROOM_TYPE window guests SQ_FT BALCONY_SQ_FT
-----------------------------------------------------------------------------------------------------
1 102 Suite Standard Ocean 4 533
2 103 statoom Standard Ocean 2 160
3 104 Suite Standard None 4 533
4 105 statoom Standard Ocean 3 205
5 106 Suite Standard None 6 586
6 107 Suite Royal Ocean 5 1524
7 108 statoom Large None 2 211
8 109 statoom Standard None 2 180
9 110 statoom Large None 2 225
10 702 Suite Presidential None 5 1142
11 703 Suite Royal Ocean 5 1745
12 704 Suite Skyloft Ocean 8 722
 
Group by without WHERE and HAVING
 
 
SQL> select room_style, room_type, sum (GUESTS) "GUEST SUM" from sys. ship_cabins group by room_style, room_type;
 
ROOM_STYLE ROOM_TYPE GUEST SUM
----------------------------------------
Statoom Standard 7
Suite Standard 14
Statoom Large 4
Suite Skyloft 8
Suite Royal 10
Suite Presidential 5
 
HAVING
 
 
SQL> select room_style, room_type, sum (GUESTS) "GUEST SUM" from sys. ship_cabins group by room_style, room_type having room_type <> 'royal'; ROOM_STYLE ROOM_TYPE GUEST SUM
----------------------------------------
Statoom Standard 7
Suite Standard 14
Statoom Large 4
Suite Skyloft 8
Suite Presidential 5
 
WHERE
 
 
SQL> select room_style, room_type, sum (GUESTS) "GUEST SUM" from sys. ship_cabins where room_type <> 'royal' group by room_style, room_type;
 
ROOM_STYLE ROOM_TYPE GUEST SUM
----------------------------------------
Statoom Standard 7
Suite Standard 14
Statoom Large 4
Suite Skyloft 8
Suite Presidential 5
 
Summary:
 
There is no difference between them when there is no aggregate function in HAVING clause.
 
However, we do this in HAVING.
 
 
SQL> select room_style, room_type, sum (GUESTS) "GUEST SUM" from sys. ship_cabins group by room_style, room_type having sum (GUESTS)> 8;
 
ROOM_STYLE ROOM_TYPE GUEST SUM
----------------------------------------
Suite Standard 14
Suite Royal 10
 
 
There is no way to filter out sum (GUESTS)> 8 entries using WHERE, because it doesn't allow to use Aggregate function in WHERE clause.

From column Coding of ye Xianyi