Skip to content

Grouping, aggregation, HAVING, and counting zero matches

Problem

  1. How to summarize many rows into totals, averages, counts, and grouped results, including groups with no matching rows?

Notions: Grouping, aggregation, HAVING, and counting zero matches

SQL and Pandas syntax

SELECT
    key,
    COUNT(*) AS row_count,
    COUNT(nullable_col) AS non_null_count,
    COUNT(DISTINCT value_col) AS distinct_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount
FROM t
WHERE amount > 0
GROUP BY key
HAVING COUNT(*) >= 2;

SELECT
    d.id,
    COUNT(f.id) AS fact_count
FROM dimension AS d
LEFT JOIN facts AS f
    ON d.id = f.dimension_id
GROUP BY d.id;

out = (
    df.loc[df["amount"] > 0]
    .groupby("key", as_index=False, dropna=False)
    .agg(
        row_count=("key", "size"),
        non_null_count=("nullable_col", "count"),
        distinct_count=("value_col", "nunique"),
        total_amount=("amount", "sum"),
        avg_amount=("amount", "mean"),
        min_amount=("amount", "min"),
        max_amount=("amount", "max")
    )
)

out = out.loc[out["row_count"] >= 2]

Example

import sqlite3
import pandas as pd

## SQL

con = sqlite3.connect(":memory:")

con.executescript("""
CREATE TABLE users (
    id INTEGER,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER,
    user_id INTEGER,
    amount REAL
);

INSERT INTO users VALUES
    (1, 'Ava'),
    (2, 'Ben'),
    (3, 'Cam');

INSERT INTO orders VALUES
    (101, 1, 20.0),
    (102, 1, 30.0),
    (103, 1, 50.0),
    (104, 2, 50.0);
""")

sql = """
-- Show all users with their matching orders, including users without orders.
SELECT *
FROM users u
LEFT JOIN orders AS o
    ON u.id = o.user_id
"""
pd.read_sql_query(sql, con)
#    id name     id  user_id  amount
# 0   1  Ava  101.0      1.0    20.0
# 1   1  Ava  102.0      1.0    30.0
# 2   1  Ava  103.0      1.0    50.0
# 3   2  Ben  104.0      2.0    50.0
# 4   3  Cam    NaN      NaN     NaN

sql = """
-- Summarize order counts and amounts per user, keeping users with no orders.
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_amount,
    COALESCE(ROUND(AVG(o.amount), 2), 0) AS avg_amount
FROM users u
LEFT JOIN orders AS o
    ON u.id = o.user_id
GROUP BY u.id
ORDER BY u.id;
"""

pd.read_sql_query(sql, con)
#    id name  order_count  total_amount  avg_amount
# 0   1  Ava            3         100.0       33.33
# 1   2  Ben            1          50.0       50.00
# 2   3  Cam            0           0.0        0.00

pd.read_sql_query("""
-- Show how ROUND truncates values.
SELECT ROUND(6.3333,2) AS r from users;
""", con)
#       r
# 0  6.33
# 1  6.33
# 2  6.33

## Pandas

users = pd.DataFrame({
    "id": [1, 2, 3],
    "name": ["Ava", "Ben", "Cam"]
})

orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "user_id": [1, 1, 1, 2],
    "amount": [20.0, 30.0, 50.0, 50.0]
})

merged = users.merge(orders, left_on="id", right_on="user_id", how="left")
merged
#    id name  order_id  user_id  amount
# 0   1  Ava     101.0      1.0    20.0
# 1   1  Ava     102.0      1.0    30.0
# 2   1  Ava     103.0      1.0    50.0
# 3   2  Ben     104.0      2.0    50.0
# 4   3  Cam       NaN      NaN     NaN

out = (
    merged.groupby(["id"], as_index=False)
    .agg(
        order_count=("order_id", "count"),
        total_amount=("amount", "sum"),
        avg_amount=("amount", "mean")
    )
)
out
#    id  order_count  total_amount  avg_amount
# 0   1            3         100.0   33.333333
# 1   2            1          50.0   50.000000
# 2   3            0           0.0         NaN

out["total_amount"] = out["total_amount"].fillna(0)
out["avg_amount"] = out["avg_amount"].fillna(0).round(2)
out
#    id  order_count  total_amount  avg_amount
# 0   1            3         100.0       33.33
# 1   2            1          50.0       50.00
# 2   3            0           0.0        0.00