# 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

```sql
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

```sql
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

```sql
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

```sql
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

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

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

```sql
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**

```sql
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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dev117uday.gitbook.io/databases/sql/tables/group-by-and-having.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.