loading..
Ðóññêèé    English
00:32

Exercise #28

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
  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) 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

  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:

1
1
1
...

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.





Tags
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
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.