<jupyter_start><jupyter_text>AggregationSQL's SELECT statement can also perform aggregation on sets of records.Let's connect to our database:<jupyter_code>import sqlalchemy as sa
import pandas as pd
engine = sa.create_engine('sqlite:///flight.db')
connection = engine.connect()
connection.execute("PRAGMA foreign_keys=on")<jupyter_output><empty_output><jupyter_text>We're expecting have three records in our flights table and sixty readings.Let's see if that's correct:<jupyter_code>pd.read_sql("SELECT COUNT(*) FROM flights", connection)
pd.read_sql("SELECT COUNT(*) FROM readings", connection)<jupyter_output><empty_output><jupyter_text>COUNT is one of SQL's aggregation functions. We also have SUM, MAX, MIN and AVG available.Let's try some examples:<jupyter_code>sql = """
    SELECT MAX(pressure)
    FROM readings 
    WHERE flight = 'hab1'
"""
pd.read_sql(sql, connection)
sql = """
    SELECT AVG(temp)
    FROM readings
    WHERE flight = 'hab3'
"""
pd.read_sql(sql, connection)<jupyter_output><empty_output><jupyter_text>We can also group our record set before aggregation using a GROUP BY clause.Let's check that our sixty readings is comprised of twenty for each flight:<jupyter_code>sql = """
    SELECT flight, COUNT(*)
    FROM readings
    GROUP BY flight
"""
pd.read_sql(sql, connection)<jupyter_output><empty_output><jupyter_text>Or find the average pressure reading for each flight:<jupyter_code>sql = """
    SELECT flight, AVG(pressure)
    FROM readings
    GROUP BY flight
"""
pd.read_sql(sql, connection)<jupyter_output><empty_output><jupyter_text>There's nothing to stop us joining multiple tables in an aggregation query.We can also use aliases in the column names.Let's find the maximum and minimum temperature reading by country:<jupyter_code>sql = """
    SELECT
        f.country_code,
        MAX(r.temp) AS max_temp,
        MIN(r.temp) AS min_temp
    FROM readings AS r JOIN flights AS f
        ON r.flight = f.name
    GROUP BY f.country_code
"""
pd.read_sql(sql, connection)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09750122488975992, 'fraction_numerical': 0.0009799118079372856, 'mean_word_length': 5.11377245508982, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '49091', 'license': 'MIT', 'n_tokens_mistral': 672, 'n_tokens_neox': 617, 'n_words': 194, 'path': '10-Aggregation.ipynb', 'repo_name': 'meatballs/sql_python_notebooks', 'size': '2041'}