loading..
Ðóññêèé    English
04:22
go

Aggregate function to aggregate function page 1

Let us consider this exercise:

Find the maximum value among the average prices of PCs, counted separately for each manufacturer.


Calculation of average cost for makers is not difficult:

Console
Execute
  1. SELECT AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model 
  3. GROUP BY maker;

However, the standard prohibits the use of a subquery as an argument to aggregate function, i.e. we can not solve the exercise by this way:

Console
Execute
  1. SELECT MAX(
  2. SELECT AVG(price) avg_price
  3. FROM Product P JOIN PC ON P.model = PC.model 
  4. GROUP BY maker
  5. );

In such cases, use a subquery in the FROM clause:

Console
Execute
  1. SELECT MAX(avg_price) 
  2. FROM (SELECT AVG(price) avg_price
  3.       FROM Product P JOIN PC ON P.model = PC.model 
  4.       GROUP BY maker
  5.      ) X;

By means of new features of language - window functions - this problem can be solved without a subquery:

Console
Execute
  1. SELECT DISTINCT MAX(AVG(price)) OVER () max_avg_price
  2.       FROM Product P JOIN PC ON P.model = PC.model 
  3.      GROUP BY maker;

Note that window functions admit use of aggregate function as argument. DISTINCT keyword is necessary here because the maximal value, which has been counted up over all set of average values, will be "attributed" to each manufacturer.

We can't use aggregate function as a parameter of another aggregate function. This implies impossibility of the solution to the above problem as follows.

  1. SELECT MAX(AVG(price)) max_avg_price
  2.     FROM Product P JOIN PC ON P.model = PC.model 
  3.     GROUP BY maker;

But there are no rules without exceptions. As it seems to be unreal, but this query is working in Oracle and returns the required result:

MAX_AVG_PRICE
850

You can assure yourself that it is true by visiting the learn-stage exercises page at sql-ex.ru, selecting Oracle in the DBMS list, and running the query (without checking solution for example).

Besides, the solution which uses window function will work in Oracle also. I can suggest that the solution without window function actually uses it implying the OVER() clause implicitly.
Pages 1 2 3
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.