Summary  
This chapter illustrates how to use the HAVING clause to filter grouped query results based on aggregate functions (e.g., COUNT, AVG) and explains its placement after GROUP BY versus WHERE’s pre-aggregation filtering.

General domain of usage  
School information systems

The school information system is being reviewed to ensure data consistency and accuracy.
As part of this review, a new data analysis task has been assigned.

During routine checks, it was discovered that some student records contain more than one grade entry, even though the system is designed to store only a single grade per student.

Your task is to help identify such cases for further internal review.

Let's think together about **how we can do this**. You could start by considering that we can do this using a `WHERE` clause, and it would look something like this:

SELECT student_surname
FROM student_grades
WHERE COUNT(grade) > 1

But, as you can see, we get an <span style="color:red">error</span> indicating that **we cannot use aggregate functions** inside a `WHERE` clause. This is where we'll need the `HAVING` clause. 

Let's say we need to **retrieve the departments** where employees' **average** salary is **below $70,000 per year**. 
To achieve this, we'll need to use an aggregate function and the `HAVING` clause:

SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) < 70000

We received one department in response using the `HAVING` clause, where we **set a condition for the column** by which we grouped the data.



**To use data aggregation** within the `HAVING` clause, we need to have **data grouping in our query**. As in the query above, we grouped the data by the `department` column.

Note

Let's look at the more generalized syntax of the `HAVING` clause and when it's best to use it:

```sql
SELECT column1, column2 --(optional)
FROM table
GROUP BY column1
HAVING AGG(column_n) --(condition)
```

Let's also briefly understand the **main difference** between `WHERE` and `HAVING` clauses and when to use each of them:

1. The `WHERE` clause is used **before** data aggregation, while the `HAVING` clause is used **after** data aggregation;
2. The `WHERE` clause is written **before** `GROUP BY`, while the `HAVING` clause is written **after** `GROUP BY`.

These are the **two main differences** you need to remember for successful use of the `HAVING` clause. Now, let's return to the task given to us by the school.

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_query_output_is_correct(self):
        """Check if the query returns only surnames of students with more than 1 grade, sorted alphabetically."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Get expected surnames
        solution_query = '''
            SELECT student_surname
            FROM student_grades
            GROUP BY student_surname
            HAVING COUNT(grade) > 1
            ORDER BY student_surname;
        '''
        self.cursor.execute(solution_query)
        expected = [row[0] for row in self.cursor.fetchall()]
        # Get user result
        self.cursor.execute(query)
        result = [row[0] for row in self.cursor.fetchall()]
        condition = result == sorted(expected)
        if not condition:
            failure_msg = f"Query result is incorrect.\\nExpected: {expected}\\nGot: {result}"
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "Query output is correct and sorted alphabetically.",
            failure_msg
        )

    def test_only_one_column(self):
        """Check that only one column (student_surname) is returned."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        condition = columns == ["student_surname"]
        if not condition:
            failure_msg = f"Query columns are incorrect. Expected ['student_surname'], got {columns}."
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "Query returns only 'student_surname' column.",
            failure_msg
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)


test_solution.py

This course is perfect for those who already have a basic understanding of SQL and want to delve into more advanced concepts to craft more powerful queries. Throughout the course, you will become familiar with data grouping and filtering grouped data. You will also learn how to work with multiple tables simultaneously, including how to combine them. Additionally, you will explore different types of table joins and how to apply them in practice.

HAVING Clause

Brief Instructions

解答