Understanding GROUP BY vs ORDER BY (and How GROUP BY Reduces Rows)

[Rajeev Bagra]

📘 Learning Post: Understanding GROUP BY vs ORDER BY (and How GROUP BY Reduces Rows

 

---

 

🎯 Problem Context

 

When working with SQL, especially in analytics or reporting, you’ll often need to:

 

Summarize data → using GROUP BY

 

Sort results → using ORDER BY

 

 

Many learners confuse these two — but they serve completely different purposes.

 

 

---

 

🧾 Example Table: schools

 

id name city

 

1 School A Delhi

2 School B Delhi

3 School C Mumbai

4 School D Mumbai

5 School E Mumbai

6 School F Kolkata

 

 

👉 Total rows = 6

 

 

---

 

🔹 Part 1: How GROUP BY Reduces Rows

 

 

---

 

❌ Without GROUP BY

 

SELECT city FROM schools;

 

📊 Output:

 

city

 

Delhi

Delhi

Mumbai

Mumbai

Mumbai

Kolkata

 

 

👉 Rows remain 6

 

 

---

 

✅ With GROUP BY

 

SELECT city FROM schools

GROUP BY city;

 

📊 Output:

 

city

 

Delhi

Mumbai

Kolkata

 

 

👉 Rows reduced from 6 → 3

 

 

---

 

🧠 Why Rows Reduce?

 

GROUP BY:

 

1. Finds unique values

 

 

1. Combines rows into groups

 

 

1. Returns one row per group

 

 

 

 

---

 

🔹 With Aggregation

 

SELECT city, COUNT(*)

FROM schools

GROUP BY city;

 

📊 Output:

 

city count

 

Delhi 2

Mumbai 3

Kolkata 1

 

 

👉 Still only 3 rows

👉 Each row = summary of a group

 

 

---

 

🔹 Part 2: What ORDER BY Does

 

 

---

 

✅ Sorting Results

 

SELECT city, COUNT(*)

FROM schools

GROUP BY city

ORDER BY COUNT(*) DESC;

 

📊 Output (Sorted):

 

city count

 

Mumbai 3

Delhi 2

Kolkata 1

 

 

👉 Same rows (3)

👉 Just reordered

 

 

---

 

🧠 Key Insight

 

ORDER BY does not reduce rows — it only changes their order.

 

 

 

 

---

 

⚖️ GROUP BY vs ORDER BY (Clear Difference)

 

Feature GROUP BY 🧩 ORDER BY 🔽

 

Purpose Group data Sort data

Effect Reduces rows Keeps same rows

Use Case Aggregation Presentation

Position Before ORDER BY Last clause

 

 

 

---

 

⚠️ Common Mistake

 

SELECT city, name

FROM schools

GROUP BY city;

 

👉 ❌ Error (ambiguous name)

 

 

---

 

✅ Correct Version

 

SELECT city, COUNT(*)

FROM schools

GROUP BY city;

 

 

---

 

🔄 How They Work Together

 

SELECT city, COUNT(*)

FROM schools

GROUP BY city

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC;

 

 

---

 

🧠 Execution Order (Very Important)

 

1. FROM

 

 

1. GROUP BY

 

 

1. HAVING

 

 

1. SELECT

 

 

1. ORDER BY

 

 

 

 

---

 

🧪 Practice Exercise

 

👉 Find cities:

 

With more than 2 schools

 

Sorted alphabetically

 

 

 

---

 

🧩 Final Takeaways

 

GROUP BY = reduces rows (creates summaries)

 

ORDER BY = sorts results (no reduction)

 

Together, they power:

 

📊 Reports

 

📈 Dashboards

 

📉 Data analysis

 

[Author]

Posts