# GROUP BY & JOIN

Code With Purpose · English · Free starter lesson

## Concept

GROUP BY gathers rows with the same key so an aggregate such as COUNT or SUM can summarize each group. WHERE filters rows before grouping; HAVING filters the grouped results. COUNT(*) counts rows, while COUNT(column) excludes NULL values in that column.

JOIN connects rows through a matching condition. INNER JOIN keeps matching rows; LEFT JOIN also keeps every left-side row and fills unmatched right-side fields with NULL. A one-to-many relationship intentionally produces multiple rows for one key.

Check the grain: what does one row mean before and after a join? Joining two tables with multiple matches per key can multiply rows and inflate sums. Validate row counts and keys. The runnable SQLite example counts orders per customer, including a customer with no orders.

## Example

```python
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1), (11, 1);
""")
print(db.execute("""
SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c LEFT JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name ORDER BY c.id
""").fetchall())
db.close()
```

## Expected output

```text
[('Asha', 2), ('Ravi', 0)]
```

## Exercise

Modify the query to keep only customers with at least two orders. Why should you count o.id rather than COUNT(*)?

## Suggested solution

```
SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c LEFT JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.id) >= 2
ORDER BY c.id;
-- Asha, 2
-- COUNT(o.id) ignores the NULL from an unmatched order.
-- COUNT(*) would count Ravi's preserved left-side row as 1.
```

## Takeaway

Check relationship cardinality before trusting an aggregate.

Reference: https://www.sqlite.org/lang_select.html

https://codewithpurposeai.in/lesson-sql-group-join.html?lang=english