GROUP BY & JOIN

Understand the 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.

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()

Output / what to expect

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

Try it yourself

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

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.

Concept samjho

GROUP BY same key wali rows ko ek group mein rakhta hai. COUNT ya SUM har group ka summary deta hai. WHERE grouping se pehle rows filter karta hai; HAVING grouping ke baad result filter karta hai. COUNT(*) rows count karta hai, COUNT(column) us column ke NULL values ko count nahi karta.

JOIN matching condition se tables connect karta hai. INNER JOIN sirf matching rows rakhta hai. LEFT JOIN left table ki har row rakhta hai aur unmatched right fields ko NULL deta hai. One-to-many relationship mein ek key ke liye multiple rows aana expected hai.

Grain check karo: join ke pehle aur baad ek row ka matlab kya hai? Dono tables mein same key par multiple matches hon toh rows multiply ho sakti hain aur sums galat ho sakte hain. Keys aur row counts validate karo. Example zero orders wale customer ko bhi count mein rakhta hai.

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()

Output / kya expect karein

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

Ab khud try karo

Query badlo taaki sirf kam se kam do orders wale customers aayein. COUNT(*) ke bajay o.id count kyun karna chahiye?

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.