6.23. SQL Select Functions

6.23.1. Sum

SELECT SUM(experience)
FROM astronauts
WHERE agency = 'NASA';

6.23.2. Average

SELECT AVG(age)
FROM astronauts
WHERE agency = 'NASA';

6.23.3. Count

SELECT COUNT(id)
FROM astronauts
WHERE agency = 'NASA';

SELECT COUNT(DISTINCT agency)
FROM astronauts;

SELECT COUNT(DISTINCT mission_name)
FROM astronauts
WHERE agency = 'NASA';

6.23.4. Assignments

# %% 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 Function Distinct
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - column: `group`
#    - only: unique values
#    - use: `DISTINCT()`
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumna: `group`
#    - tylko: unikalne wartości
#    - użyj: `DISTINCT()`
# 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': 'users'},
 {'group': 'admins'},
 {'group': 'staff'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: `group`
# - only: unique values
# - use: `DISTINCT()`
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 Function Count
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `products`
#    - columns: all
#    - what: count number of rows
#    - use: `COUNT()`
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `products`
#    - kolumny: all
#    - co: zlicz liczbę wierszy
#    - użyj: `COUNT()`
# 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=79)
[{'COUNT(*)': 25}]
"""

# Write SQL query to select data:
# - table: `products`
# - columns: all
# - what: count number of rows
# - use: `COUNT()`
result = """

SELECT *
FROM `products`

"""

# %% 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 Function Sum
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `products`
#    - columns: `price`
#    - what: sum of prices of all products
#    - use: `SUM()`
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `products`
#    - kolumny: `price`
#    - co: suma cen wszystkich produktów
#    - użyj: `SUM()`
# 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=79)
[{'SUM(`price`)': 11003.64}]
"""

# Write SQL query to select data:
# - table: `products`
# - columns: `price`
# - what: sum of prices of all products
# - use: SUM()
result = """

SELECT `price`
FROM `products`

"""

# %% 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 Function Avg
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `products`
#    - columns: `price`
#    - what: average price of all products
#    - use: AVG()
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `products`
#    - kolumny: `price`
#    - co: średnią cenę wszystkich produktów
#    - użyj: AVG()
# 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=79)
[{'AVG(`price`)': 440.1456}]
"""

# Write SQL query to select data:
# - table: `products`
# - columns: price
# - what: average price of all products
# - use: AVG()
result = """

SELECT `price`
FROM `products`

"""

# %% 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 Function Min
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `products`
#    - column: `name`, `price`
#    - what: cheapest product
#    - use: MIN()
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `products`
#    - kolumna: `name`, `price`
#    - co: najtańszy produkt
#    - użyj: MIN()
# 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=79)
[{'name': 'November', 'MIN(`price`)': 12.0}]
"""

# Write SQL query to select data:
# - table: `products`
# - column: `name`, `price`
# - what: cheapest product
# - use: MIN()
result = """

SELECT `name`, `price`
FROM `products`

"""

# %% 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 Function Max
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `products`
#    - column: `name`, `price`
#    - what: the most expensive product
#    - use: MAX()
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `products`
#    - kolumna: `name`, `price`
#    - co: majdroższy produkt
#    - użyj: MAX()
# 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=79)
[{'name': 'Romeo', 'MAX(`price`)': 1337.0}]
"""

# Write SQL query to select data:
# - table: `products`
# - column: `name`, `price`
# - what: the most expensive product
# - use: MAX()
result = """

SELECT `name`, `price`
FROM `products`

"""