How Do `COUNT(*)` and `GROUP BY` Work Together in SQL, Even Though They Appear on Different Lines?

[Rajeev Bagra]

While learning SQL aggregation, I was working on a query to find cities in Massachusetts with the most public schools using this code:

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

I noticed that COUNT(*) is written in the SELECT line at the top, while GROUP BY city appears later.

So my question is:

Are COUNT(*) and GROUP BY dependent on each other?
Does SQL “understand” how to count only after it reaches the GROUP BY line?

How does this work internally?

---

✅ Answer

Yes — COUNT(*) and GROUP BY are closely connected.
They work together, even though they appear on different lines.

More importantly:

SQL does not execute queries from top to bottom the way humans read them.

Instead, SQL follows a logical processing order.

---

ߔ How SQL Really Processes Your Query

Although you write:

SELECT ...
FROM ...
GROUP BY ...

SQL internally processes it like this:

1️⃣ FROM schools
2️⃣ WHERE type = 'Public School'
3️⃣ GROUP BY city
4️⃣ Apply COUNT(*) to each group
5️⃣ SELECT final columns
6️⃣ ORDER BY
7️⃣ LIMIT

So in reality:

• SQL first gathers the data
• Then groups it
• Then counts inside each group
• Then displays results

This means:

COUNT(*) only makes sense after GROUP BY has created groups.

---

ߔ Why COUNT(*) Still Appears in SELECT

The SELECT clause is where you declare what you want to see, not when it is calculated.

When you write:

SELECT city, COUNT(*)

You are telling SQL:

“In the final output, I want the city name and the count.”

Even though SQL calculates later, you describe it first.

This is similar to writing a math formula before solving it.

---

ߔ What GROUP BY Actually Does

When you write:

GROUP BY city

SQL groups rows that have the same city.

For example:

name	city
School A	Boston
School B	Boston
School C	Cambridge

Becomes:

Boston group

• School A
• School B

Cambridge group

• School C

After this grouping, COUNT(*) counts rows inside each group.

---

ߔ What Happens Without GROUP BY

Without grouping:

SELECT COUNT(*)
FROM schools;

SQL treats the whole table as one group.

Result:

Total number of schools in the state.

With grouping:

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

Result:

Number of schools per city.

So the presence of GROUP BY changes how COUNT(*) behaves.

---

ߔ Why COUNT(*) Depends on GROUP BY

Without GROUP BY:

COUNT(*) → one total

With GROUP BY:

COUNT(*) → many small totals (one per city)

So yes:

COUNT(*) depends on GROUP BY to know how to count.

---

ߔ Important SQL Rule

This query is invalid:

SELECT name, COUNT(*)
FROM schools;

Because:

• name is not grouped
• COUNT(*) is aggregated

SQL does not know how to mix them.

But this works:

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

Because now everything is logically grouped.

---

ߧ Easy Way to Remember

Use this mental model:

GROUP BY = create buckets
COUNT(*) = count items in each bucket

In this example:

• Buckets → cities
• Items → schools

So:

Count how many schools are in each city.

---

✅ Final Working Example

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

This query:

✔ Filters public schools
✔ Groups by city
✔ Counts schools per city
✔ Sorts by highest count
✔ Shows top 10 results

---

ߓ Key Takeaway

Even though COUNT(*) appears at the top of your query:

SQL only understands how to apply it after reading GROUP BY.

So they are logically connected, even if they are written on different lines.

---

[Author]

Posts