SQLite GROUP BY

The GROUP BY clause divides rows into groups based on one or more columns, then applies an aggregate function to each group separately. Instead of one summary for the entire table, you get one summary per group.

The Grouping Analogy

Imagine sorting receipts into separate envelopes by department:

UNSORTED RECEIPTS          AFTER GROUP BY department
────────────────────       ──────────────────────────────
Alice  │ HR    │ 500       [HR Envelope]
Bob    │ Sales │ 800         Alice  500
Carol  │ HR    │ 450         Carol  450
Dan    │ Sales │ 700         Total: 950
Eve    │ IT    │ 600
Frank  │ IT    │ 550       [Sales Envelope]
                             Bob    800
                             Dan    700
                             Total: 1500

                           [IT Envelope]
                             Eve    600
                             Frank  550
                             Total: 1150

Sample Table

orders
──────────────────────────────────────────────────────────
id │ customer │ category    │ product      │ amount │ month
───┼──────────┼─────────────┼──────────────┼────────┼──────
1  │ Alice    │ Electronics │ Laptop       │ 1200   │ Jan
2  │ Bob      │ Stationery  │ Notebook     │ 15     │ Jan
3  │ Alice    │ Stationery  │ Pen Pack     │ 25     │ Feb
4  │ Carol    │ Electronics │ Tablet       │ 600    │ Jan
5  │ Bob      │ Electronics │ Phone        │ 800    │ Feb
6  │ Carol    │ Stationery  │ Sticky Notes │ 10     │ Feb
7  │ Alice    │ Electronics │ Headphones   │ 250    │ Feb

GROUP BY One Column

-- Total spending per customer
SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

customer │ total_spent
─────────┼────────────
Alice    │ 1475
Bob      │ 815
Carol    │ 610

GROUP BY with Multiple Aggregates

-- Orders and revenue per category
SELECT
  category,
  COUNT(*)     AS order_count,
  SUM(amount)  AS revenue,
  AVG(amount)  AS avg_order
FROM orders
GROUP BY category;

category    │ order_count │ revenue │ avg_order
────────────┼─────────────┼─────────┼──────────
Electronics │ 4           │ 2850    │ 712.5
Stationery  │ 3           │ 50      │ 16.67

GROUP BY Multiple Columns

Grouping by two columns creates one row for every unique combination of those two column values.

-- Revenue per customer per category
SELECT customer, category, SUM(amount) AS total
FROM orders
GROUP BY customer, category
ORDER BY customer, category;

customer │ category    │ total
─────────┼─────────────┼───────
Alice    │ Electronics │ 1450
Alice    │ Stationery  │ 25
Bob      │ Electronics │ 800
Bob      │ Stationery  │ 15
Carol    │ Electronics │ 600
Carol    │ Stationery  │ 10

GROUP BY with ORDER BY

-- Show categories sorted by total revenue, highest first
SELECT category, SUM(amount) AS revenue
FROM orders
GROUP BY category
ORDER BY revenue DESC;

category    │ revenue
────────────┼─────────
Electronics │ 2850
Stationery  │ 50

GROUP BY with WHERE

WHERE filters rows before grouping. Only rows passing the WHERE condition enter the groups.

-- Revenue per customer for February orders only
SELECT customer, SUM(amount) AS feb_total
FROM orders
WHERE month = 'Feb'
GROUP BY customer;

customer │ feb_total
─────────┼──────────
Alice    │ 275    (25 + 250)
Bob      │ 800
Carol    │ 10

Execution Order: WHERE then GROUP BY

Query execution sequence:
─────────────────────────────────────────────────────
1. FROM orders         → load all 7 rows
2. WHERE month='Feb'   → keep 4 rows (Feb only)
3. GROUP BY customer   → form 3 groups
4. SELECT + SUM        → compute one result per group
5. ORDER BY            → sort final output

GROUP BY and NULL

NULL values form their own group. All rows with NULL in the GROUP BY column cluster together.

TABLE: tasks
id │ assigned_to │ status
───┼─────────────┼──────────
1  │ Alice       │ done
2  │ NULL        │ pending
3  │ Alice       │ pending
4  │ NULL        │ done

SELECT assigned_to, COUNT(*) AS task_count
FROM tasks
GROUP BY assigned_to;

assigned_to │ task_count
────────────┼────────────
Alice       │ 2
NULL        │ 2          ← NULLs form their own group

Rules for Columns in SELECT with GROUP BY

In standard SQL and SQLite, every column in the SELECT list must either be in the GROUP BY clause or wrapped in an aggregate function. SQLite is lenient here — it allows non-aggregated columns, but the values returned for those columns are undefined.

-- CORRECT: every SELECT column is either grouped or aggregated
SELECT customer, SUM(amount)
FROM orders
GROUP BY customer;

-- RISKY in other databases (SQLite allows it, result is arbitrary):
SELECT customer, product, SUM(amount)
FROM orders
GROUP BY customer;
-- "product" is neither grouped nor aggregated — unpredictable

Key Takeaways

• GROUP BY col creates one summary row per unique value in col
• Combine with COUNT, SUM, AVG, MIN, MAX to summarize each group
• WHERE filters rows before grouping; HAVING filters groups after grouping
• Group by multiple columns to get per-combination summaries
• NULL values form their own separate group