Common mistakes in solving SELECT statement exercises

Computer Firm Database

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:

Console

Execute

SELECT COUNT(Maker) AS qnty
FROM Product
GROUP BY maker
HAVING COUNT(model)=1;

SELECT COUNT(Maker) AS qnty FROM Product GROUP BY maker HAVING COUNT(model)=1;

    Scheme

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

COUNT(maker) = COUNT(model) = COUNT(*) = COUNT(1) = ...

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:

1. Records in the Product table are grouped by maker, with the number of records (models) being counted for each maker.
2. A filtering of the resulting groups limiting these quantities to the value 1 is performed.

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:

Getting totals.

Aggregate function within aggregate function.