CODEWITHSUNDEEP
mysqlsql
Scary
Scary

Reputation: 91

sql having clause issue

Q: Retrieve the count of models produced by each make in only 2002. Order the results by make. Exclude makes who only produce less than 5 models by using a having clause.

What I have:

select count(model) from vehicle where year="2002" order by make having count(model) > 5

This gives an error for the having clause. Anyone know?

Upvotes: 2

Views: 124

Answers (1)

kjmerf
kjmerf

Reputation: 4345

I think you might want:

select make, count(*)
from vehicle
where year="2002"
group by make
having count(*) >= 5
order by make

Let me walk you through it. In your select statement, you want make and then a count of how many records you have per make, which is expressed as count(*). You are picking from the rows in vehicle where year is 2002. That part you had right. Then you need to use a group by statement. You can't count anything if you don't group together the records you want to count. For you, combine the records with the same make. Then you can specify each group should have at least 5 records.

This is an important point - a WHERE clause must be used to modify a table before a GROUP BY statement. Once you group your table, you have to use HAVING but you cannot use HAVING without GROUP BY.

Upvotes: 2

Related Questions