Summary  
This chapter covers how to use the HAVING clause to filter results of grouped queries with aggregate functions and explains when to use HAVING versus WHERE.  

General domain of usage  
Data analysis in relational databases

Koulun tietojärjestelmää tarkastellaan tietojen johdonmukaisuuden ja tarkkuuden varmistamiseksi.
Osana tätä tarkastelua on annettu uusi data-analyysitehtävä.

Rutiinitarkistusten aikana havaittiin, että joissakin opiskelijatiedoissa on useampi kuin yksi arvosanamerkintä, vaikka järjestelmä on suunniteltu tallentamaan vain yksi arvosana opiskelijaa kohden.

Tehtävänäsi on auttaa tunnistamaan tällaiset tapaukset jatkotarkastelua varten.

Pohdi **miten voit tehdä tämän**. Voisit aloittaa miettimällä, että tämän voi tehdä käyttämällä `WHERE`-lausetta, ja se näyttäisi suunnilleen tältä:

SELECT student_surname
FROM student_grades
WHERE COUNT(grade) > 1

Kuten huomaat, saamme <span style="color:red">virheilmoituksen</span>, joka osoittaa, että **et voi käyttää aggregaattifunktioita** `WHERE`-ehdon sisällä. Tässä kohtaa tarvitset `HAVING`-ehdon.

Oletetaan, että sinun täytyy **hakea osastot**, joissa työntekijöiden **keskiarvopalkka** on **alle 70 000 dollaria vuodessa**.
Tämän saavuttamiseksi tarvitsemme aggregaattifunktion ja `HAVING`-ehdon:

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

Tuloksena on yksi osasto vastauksena käyttämällä `HAVING`-lausetta, jossa **asetetaan ehto sarakkeelle**, jota käytetään ryhmittelyssä.



**Tietojen aggregoinnin käyttö** `HAVING`-lauseessa edellyttää, että kyselyssä on **tietojen ryhmittely**. Kuten yllä olevassa kyselyssä, ryhmittelimme tiedot `department`-sarakkeen mukaan.

Huomio

Tässä on yleistetty syntaksi `HAVING`-lauseelle sekä tilanteet, joissa sen käyttö on suositeltavaa:

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

-- This will cause an error
```

Tässä on lyhyt katsaus **pääeroihin** `WHERE`- ja `HAVING`-lauseiden välillä sekä siihen, milloin kumpaakin käytetään:

1. `WHERE`-lausetta käytetään **ennen** tietojen aggregointia, kun taas `HAVING`-lausetta käytetään **aggregoinnin jälkeen**;
2. `WHERE`-lause kirjoitetaan **ennen** `GROUP BY` -lausetta, kun taas `HAVING`-lause kirjoitetaan **`GROUP BY` -lauseen jälkeen**.

Nämä ovat **kaksi tärkeintä eroa**, jotka tulee muistaa `HAVING`-lauseen onnistuneessa käytössä. Nyt palataan koulun antamaan tehtävään.

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

Vie tietämyksesi seuraavalle tasolle edistyneillä SQL-tekniikoilla, joita käytetään todellisessa datatyössä. Opit ryhmittelemään ja suodattamaan tietoja GROUP BY- ja HAVING-lauseilla, kirjoittamaan sisäkkäisiä alikyselyitä sekä yhdistämään tauluja INNER-, LEFT-, RIGHT- ja FULL JOIN -tyypeillä. Lisäksi luot, muutat ja muokkaat tauluja DDL-lauseilla sekä lisäät, päivität ja poistat tietueita DML-lauseilla.

Opi ryhmittelemään tietoja GROUP BY -lauseella ja tiivistämään niitä aggregaattifunktioilla, kuten COUNT, SUM ja AVG. Tutustu ryhmiteltyjen tulosten suodattamiseen HAVING-lauseella sekä SQL-lauseiden oikeaan järjestykseen kyselyssä.



Opi kirjoittamaan sisäkkäisiä alikyselyitä WHERE- ja FROM-lausekkeissa monimutkaisempien kysymysten ratkaisemiseksi. Ymmärrä, miten yhdistetään kyselyiden tuloksia useista tauluista UNION-lauseen avulla.



Opi yhdistämään useita tauluja käyttämällä INNER-, LEFT-, RIGHT- ja FULL JOIN -tyyppejä. Harjoittele liitosten kirjoittamista, joiden avulla voit hakea toisiinsa liittyvää dataa tauluista yhtenäisenä tuloksena.

Tutustu SQL:n DDL- ja DML-perusteisiin: taulujen luominen CREATE-komennolla ja rajoitteilla sekä niiden muokkaaminen ALTER-, DROP- ja TRUNCATE-komennoilla. Tietueiden lisääminen, päivittäminen ja poistaminen INSERT-, UPDATE- ja DELETE-lauseilla.

Haaste: HAVING-lause

Lyhyet ohjeet

Ratkaisu