Exercise #28

Using the Product table, find out the number of makers who produce only one model.

The following beginners solution has been sent to the Help Desk quite often:

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

In addition to this solution being incorrect, the functions COUNT(maker)/ COUNT(model) give away an SQL newbie. COUNT(maker) doesnt 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 cant be NULL. Since, according to the database schema, maker cant be NULL either, we have

  1. 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 youll agree its quite different from whats originally been asked for in exercise 28. Although now, it doesnt 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 doesnt help you to solve the exercise, try reading the following topics:

Getting totals.

Aggregate function within aggregate function.

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.