Learn SQL - Filter after aggregate by groups

Language Help SQL
,
1

Course: Learn SQL: Aggregate Functions
Project: Trends in startups

Task 12:
“Next, filter the result to only include categories that have more than three companies in them.
What are the most competitive markets?”

Without “more than three filter”, it works.
/---------------------------------------/
select category, count()
from startups
group by category
order by count() desc
;
/--------------------------------------/

When I tried to add the filter, it doesn’t works.
/--------------------------------------/
select category, count()
from startups
**where count()>3**
group by category
order by count(*) desc
;
/--------------------------------------/

Where is the mistake?
How can I make it work properly?

2

Could you please link the lesson and format future code- How do I format code in my posts? so that asterisks and similar markings don’t go walkabouts :slightly_smiling_face:, it is much much easier for others to read.

If you’ve not looked into execution order in SQL before it might be a good idea to read up on it now; it’s very useful to know. The WHERE clause operates before the grouping so you are filtering too early. The HAVING clause works in almost the same manner as WHERE but operates after any grouping or aggregate functions.

2 Likes
3

WHERE filters rows.
HAVING filters groups.

2 Likes
4

Testing the format code and link lessonLearn SQL: Aggregate Functions:

select category, count()
from startups
group by category
order by count() desc
;
1 Like