loading..
Русский    English
18:56
go

Exercise #11 (tips and solutions)

Existence of standard aggregate function AVG solves all issues:

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

However the forum shows the attempts to be distant from the general "grey" mass. Here is quite equivalent solution which, the truth, adds the extra operation in the procedural plan:

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

Correctness of the following decision depends on available restrictions:

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

Namely, if SUM (speed) ignores rows with NULL value of speed, expression SUM(1) will count up total of rows in PC table, which, in essence, is equivalent to use of COUNT(*). As a result the sum of speeds of all the personal computers except for rows with unknown speed will be calculated in numerator which will be divided by the total number of rows. Therefore all the presented solutions will be equivalent, if NULL values are inadmissible, i.e. there is restriction NOT NULL on a speed column .

To return to discussion of exercise #11

To solve a problem on 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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME 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.
Rambler's Top100