Using the Product table, find out the number of makers who produce only one model.
The following beginner’s solution has been sent to the Help Desk quite often:
In addition to this solution being incorrect, the functions COUNT(maker)/ COUNT(model) give away an SQL newbie. COUNT(maker) doesn’t count the number of makers, but rather the number of lines in a group with maker being not NULL. COUNT(model) turns out to be equal to the number of models, yet not because the argument model has been used, but because each row in the table represents a model, and the model field is a key and can’t be NULL. Since, according to the database schema, maker can’t be NULL either, we have
All these expressions return the number of models in a group, namely the number of models produced by a single maker, since the data is grouped by the name of the maker.
Thus, that is what this query does:
The result is:
There will be as many such rows as there are makers manufacturing one model. Thus, the query under consideration fulfills the following task:
Get the number of models for each maker manufacturing one model. Output: number of models.
I think you’ll agree it’s quite different from what’s originally been asked for in exercise 28. Although now, it doesn’t take much to solve the task – the records just have to be counted. This can be done by using the query above as a subquery (or CTE).
If this hint doesn’t help you to solve the exercise, try reading the following topics:
Aggregate function within aggregate function.