Understanding How COUNT(*) Works with GROUP BY in SQL

[Rajeev Bagra]

 

(Does COUNT(*) Depend on the Column in SELECT?)

---

✅ Introduction

When learning SQL, many beginners assume that COUNT(*) depends on the column written before it in the SELECT statement.

For example, when seeing this query:

SELECT city, COUNT(*)
FROM schools
GROUP BY city;

A common question is:

Does COUNT(*) count “by city” because city comes before it in SELECT?

Or:

If I replace city with state, will COUNT(*) start counting states?

In this tutorial, we will clarify how COUNT(*) really works and what controls it.

---

📌 Initial Context

Imagine you are working with a database of schools.
Your task is to find how many schools exist in each city.

You write this query:

SELECT city, COUNT(*) AS total_schools
FROM schools
GROUP BY city;

It returns something like:

city	total_schools
Boston	35
Cambridge	12

This works perfectly — but why?

Let’s understand what is happening behind the scenes.

---

🧠 Key Rule in SQL Aggregation

COUNT(*) does not depend on what appears before it in SELECT.
It depends entirely on GROUP BY.

The column inside GROUP BY decides how data is grouped.

The function COUNT(*) simply counts rows inside those groups.

---

🔍 Step 1: What GROUP BY Does

When you write:

GROUP BY city

SQL does this:

“Put all rows with the same city into one group.”

Example table:

name	city
School A	Boston
School B	Boston
School C	Cambridge

After grouping:

Boston group

• School A
• School B

Cambridge group

• School C

Each city becomes a “bucket” of rows.

---

🔢 Step 2: What COUNT(*) Does

When you use:

COUNT(*)

SQL counts how many rows are in each bucket.

So:

• Boston → 2 schools
• Cambridge → 1 school

That is why the result is “schools per city”.

---

📍 Important: SELECT Does NOT Control Counting

Look at this query:

SELECT city, COUNT(*)
FROM schools
GROUP BY city;

Many beginners think:

“COUNT(*) counts by city because city is written first.”

This is not true.

The order in SELECT does not affect counting.

Only GROUP BY matters.

---

🧪 Experiment: Remove City from SELECT

Try this:

SELECT COUNT(*)
FROM schools
GROUP BY city;

This still works.

It returns:

2
1

But now:

• You see the counts
• You don’t know which city they belong to

So:

city in SELECT is for display, not for controlling COUNT.

---

🔄 What If We Change the Grouping?

Suppose the table also has a state column.

Group by State

SELECT state, COUNT(*)
FROM schools
GROUP BY state;

Now SQL:

• Groups by state
• Counts schools per state

Result:

state	COUNT(*)
MA	1761

So:

Changing GROUP BY changes what COUNT(*) measures.

---

❌ Example of an Invalid Query

This query is wrong:

SELECT state, COUNT(*)
FROM schools
GROUP BY city;

Why?

Because:

• You grouped by city
• But selected state

SQL does not know which state belongs to each city group.

So it throws an error.

---

✅ Correct Rule

Every column in SELECT must be:

• In GROUP BY, OR
• Inside an aggregate function (COUNT, SUM, AVG, etc.)

---

🧠 Easy Memory Trick

Remember this:

GROUP BY = creates buckets
COUNT(*) = counts items in buckets
SELECT = shows the result

So:

• GROUP BY decides “per what”
• COUNT decides “how many”
• SELECT decides “what to display”

---

📘 Final Example (Best Practice)

SELECT city, COUNT(*) AS total_schools
FROM schools
WHERE type = 'Public School'
GROUP BY city
ORDER BY total_schools DESC;

This query:

✔ Filters public schools
✔ Groups by city
✔ Counts schools per city
✔ Sorts by highest count

---

🏁 Key Takeaways

1️⃣ COUNT(*) does not depend on SELECT order
2️⃣ GROUP BY controls how counting works
3️⃣ SELECT only displays grouped data
4️⃣ Changing GROUP BY changes what COUNT measures
5️⃣ All selected columns must match GROUP BY rules

---

⭐ One-Line Summary

In SQL, GROUP BY controls counting.
COUNT(*) only follows it.

 

[Author]

Posts