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 Stateroom Standard Ocean 2 160
3 104 Suite Standard None 4 533
4 105 Stateroom Standard Ocean 3 205
5 106 Suite Standard None 6 586
6 107 Suite Royal Ocean 5 1524
7 108 Stateroom Large None 2 211
8 109 Stateroom Standard None 2 180
9 110 Stateroom 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
---------- -------------------- ----------
Stateroom Standard 7
Suite Standard 14
Stateroom 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
---------- -------------------- ----------
Stateroom Standard 7
Suite Standard 14
Stateroom 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
---------- -------------------- ----------
Stateroom Standard 7
Suite Standard 14
Stateroom 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 |