6.20. SQL Select Group By

6.20.1. Group By

SELECT
    firstname,
    lastname,
    agency
FROM astronauts
GROUP BY agency;

6.20.2. Having

SELECT
    firstname,
    lastname,
    agency,
    COUNT(id) as headcount
FROM astronauts
GROUP BY agency
HAVING COUNT(headcount) > 5;

6.20.3. Use Case - 1

SELECT
    message,
    level,
    COUNT(level) AS count
FROM logs
WHERE
    (datetime <= '1969-07-18' OR datetime >= '1969-07-20')
    AND message LIKE 'Max__%'
    AND level IN (
        SELECT DISTINCT(level) FROM logs
    )
GROUP BY level
HAVING count > 5
ORDER BY datetime DESC
LIMIT 5;

6.20.4. Assignments

# FIXME: zmienić kolumnę group na role, bo myli się z GROUP BY

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Group By
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - column: `group`
#    - what: unique group names
#    - use: GROUP BY
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumna: `group`
#    - jakie: unikalne nazwy group
#    - użyj: GROUP BY
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=20)
[{'group': 'admins'},
 {'group': 'staff'},
 {'group': 'users'}]
"""

# Write SQL query to select data:
# - table: `users`
# - column: `group`
# - what: unique group names
# - use: GROUP BY
result = """

SELECT `group`
FROM `users`

"""

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Group Having
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - column: `group`
#    - what: unique group names
#    - where: has 2 or more members
#    - use: GROUP BY and HAVING
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumna: `group`
#    - jakie: unikalne nazwy grup
#    - gdzie: mająca 2 lub więcej członków
#    - użyj: GROUP BY oraz HAVING
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=20)
[{'group': 'admins'},
 {'group': 'users'}]
"""

# Write SQL query to select data:
# - table: `users`
# - column: `group`
# - what: unique group names
# - where: has 2 or more members
# - use: GROUP BY and HAVING
result = """

SELECT `group`
FROM `users`
GROUP BY `group`

"""