GROUP BY and HAVING

GROUP BY

GROUP BY clause divide the rows returned from SELECT statement into groups
For each group, you can apply aggregate functions like COUNT, SUM, MIN, MAX etc.

Syntax

SELECT column1,
       AGGREGATE_FUNCTION(column2)
FROM tablename
GROUP BY column1;

Group BY

Group the data in column and pass it to aggregate function

Group BY with count

SELECT 
    movie_lang,
    COUNT(movie_lang) as count
FROM
    movies
GROUP BY
    movie_lang
ORDER BY 
    count ASC;

 movie_lang | count 
------------+-------
 Swedish    |     1
 German     |     1
 Korean     |     1
 Spanish    |     1
 Portuguese |     2
 Japanese   |     4
 Chinese    |     5
 English    |    38

Group BY with SUM

SELECT 
    age_certificate,
    SUM(movie_length) 
FROM 
    movies 
GROUP BY 
    age_certificate;

 age_certificate | sum  
-----------------+------
 PG              | 1462
 15              | 2184
 12              | 1425
 18              |  994
 U               |  620

Group BY with MIN, MAX

SELECT movie_lang,
       MIN(movie_length),
       MAX(movie_length)
FROM movies
GROUP BY movie_lang

 movie_lang | min | max 
------------+-----+-----
 Portuguese | 140 | 145
 German     | 165 | 165
 Chinese    |  99 | 139
 English    |  87 | 168
 Swedish    | 128 | 128
 Spanish    |  98 |  98
 Korean     | 130 | 130
 Japanese   | 107 | 120

SELECT 
    movie_lang,
    MIN(movie_length),
    MAX(movie_length)
FROM
    movies
GROUP BY
    movie_lang
ORDER BY MAX(movie_length) DESC;

 movie_lang | min | max 
------------+-----+-----
 English    |  87 | 168
 German     | 165 | 165
 Portuguese | 140 | 145
 Chinese    |  99 | 139
 Korean     | 130 | 130
 Swedish    | 128 | 128
 Japanese   | 107 | 120
 Spanish    |  98 |  98

HAVING

We use HAVING clause to specify a search condition for a group or an aggregate
The HAVING clause is often used with the GROUP BY clause to filter rows based on filter condition
cannot use column alias with having clause because it is evaluated before the SELECT statement

SELECT 
    column1,
    AGGREGATE_FUNCTION(column2),
FROM tablename
GROUP BY column1
HAVING 
    condition;

HAVING AGGREGATE_FUNCTION(column2) = value
HAVING AGGREGATE_FUNCTION(column2) >= value

SELECT 
    movie_lang, 
    SUM(movie_length) 
FROM 
    movies 
GROUP BY 
    movie_lang 
HAVING SUM(movie_length) > 200 
ORDER BY SUM(movie_length);

 movie_lang | sum  
------------+------
 Portuguese |  285
 Japanese   |  446
 Chinese    |  609
 English    | 4824

HAVING vs WHERE

HAVING works on result group
WHERE works on SELECT columns and not on the result group

SELECT
    movie_lang,
    SUM(movie_length)
FROM 
    movies
GROUP BY movie_lang
ORDER BY 2 DESC;

 movie_lang | sum  
------------+------
 English    | 4824
 Chinese    |  609
 Japanese   |  446
 Portuguese |  285
 German     |  165
 Korean     |  130
 Swedish    |  128
 Spanish    |   98