Summary  
This chapter introduces the HAVING clause in SQL to filter grouped query results based on aggregate functions after performing a GROUP BY, since WHERE cannot be used with aggregates.  

General domain of usage  
Database reporting and analytics

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.

Think about **how you can do this**. You could start by considering that you 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 **you cannot use aggregate functions** inside a `WHERE` clause. This is where you'll need the `HAVING` clause. 

Suppose you 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

The result is one department in response using the `HAVING` clause, where a **condition is set for the column** by which used for grouping.



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

Here is 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)

-- This will cause an error
```

Here is a brief look at 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, 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.

In this section, you will learn how to group and manage data effectively. We will introduce the GROUP BY operator for grouping data. Additionally, you will learn how to filter grouped data using the HAVING operator.

You will learn about nested subqueries, how to create them, and how to use them effectively.

Here, you will learn how to combine multiple tables, allowing you to work with a unified table and simplify query creation.

Learn the fundamentals of Data Definition Language (DDL) and Data Manipulation Language (DML) in SQL, including how to create, modify, and delete database objects, as well as how to insert, update, and delete data within tables.

HAVING Clause

Brief Instructions

Solution