loading..
Русский    English
15:59
go

Exercise #11 (tips and solutions)

The existence of the standard AVG aggregate function helps to fix all issues:

Console
Execute
  1. SELECT AVG(speed) 
  2. FROM PC;

However, the corresponding forum thread on the website shows the attempts to stand out from the "faceless mass. Here is quite a tantamount solution which, however, adds an extra operation to the query plan:

Console
Execute
  1. SELECT SUM(speed)/COUNT(speed) 
  2. FROM PC;

The correctness of the following solution depends on the available restrictions:

Console
Execute
  1. SELECT SUM(speed)/SUM(1) 
  2. FROM PC;

Namely, while SUM (speed) ignores rows with NULL values in the speed column, SUM(1) counts up all rows in the PC table and is essentially equivalent to COUNT(*). Hence, the numerator will contain the sum of speeds of all personal computers except for rows with unknown speed, which will be divided by the total number of rows. Therefore, the third solution will be equivalent to the other two only if no NULL values are allowed, i.e. a NOT NULL constraint is applied to the speed column.

Return to discussion of exercise #11

Solve this task at SQL-EX.RU
Bookmark and Share
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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.