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

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

Rutiinitarkastusten 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 tämän voisi tehdä**. Voisit aloittaa miettimällä, että tämän voisi toteuttaa käyttämällä `WHERE`-ehtoa, ja se näyttäisi suunnilleen tältä:

SELECT student_surname
FROM student_grades
WHERE COUNT(grade) > 1

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

Oletetaan, että haluat **hakea osastot**, joissa työntekijöiden **keskiarvopalkka** on **alle 70 000 dollaria vuodessa**.
Tämän saavuttamiseksi tarvitaan aggregaattifunktiota ja `HAVING`-ehtoa:

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

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



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

Huomio

Tässä on yleistetty syntaksi `HAVING`-ehdolle 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`-ehtolauseiden välillä sekä siihen, milloin kumpaakin käytetään:

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

Nämä ovat **kaksi tärkeintä eroa**, jotka tulee muistaa `HAVING`-ehtolauseen onnistuneessa käytössä. Palaa nyt 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

Tämä kurssi sopii erinomaisesti niille, joilla on jo perustiedot SQL:stä ja jotka haluavat syventyä edistyneempiin käsitteisiin tehokkaampien kyselyiden laatimiseksi. Kurssin aikana tutustut tietojen ryhmittelyyn ja ryhmiteltyjen tietojen suodattamiseen. Opit myös työskentelemään useiden taulukoiden kanssa samanaikaisesti, mukaan lukien niiden yhdistäminen. Lisäksi perehdyt erilaisiin taulukoiden yhdistämistyyppeihin ja niiden käytännön soveltamiseen.

Tässä osiossa opit ryhmittelemään ja hallitsemaan tietoja tehokkaasti. Esittelemme GROUP BY -operaattorin tietojen ryhmittelyyn. Lisäksi opit suodattamaan ryhmiteltyjä tietoja HAVING-operaattorin avulla.

Opit sisäkkäisistä alikyselyistä, niiden luomisesta ja tehokkaasta käytöstä.

Tässä opit yhdistämään useita tauluja, mikä mahdollistaa yhtenäisen taulun käytön ja yksinkertaistaa kyselyiden luomista.

Opi SQL:n Data Definition Language (DDL) ja Data Manipulation Language (DML) -kielten perusteet, mukaan lukien tietokantaobjektien luominen, muokkaaminen ja poistaminen sekä tietojen lisääminen, päivittäminen ja poistaminen tauluista.

HAVING-Ehto

Lyhyet ohjeet

Ratkaisu