# GROUP BY & JOIN

Code With Purpose · Hinglish · Free starter lesson

## Concept

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.

## 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

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

## 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

Aggregate par trust karne se pehle relationship aur row count check karo.

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

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