15:51

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

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

SELECT AVG(price) avg_price
FROM Product P JOIN PC ON P.model = PC.model 
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

SELECT MAX(
SELECT AVG(price) avg_price
FROM Product P JOIN PC ON P.model = PC.model 
GROUP BY maker
);

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

Console

Execute

SELECT MAX(avg_price) 
FROM (SELECT AVG(price) avg_price
      FROM Product P JOIN PC ON P.model = PC.model 
      GROUP BY maker
     ) X;

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

Console

Execute

SELECT DISTINCT MAX(AVG(price)) OVER () max_avg_price
      FROM Product P JOIN PC ON P.model = PC.model 
     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.

Pages

AVG MAX aggregate functions window functions

Previos [HAVING clause]

[Using multiple tables in a query] Next

Пусеты с рубинами

Серьги, кольца, подвески, колье и проч. Каталог с фотографиями.

goldbowl.ru
Разработка и продвижение веб сайтов

Портфолио выполненных работ. Контактная информация.

webtema.ru

Last added:

Tags

aggregate functions ALL ASCII AVG Battles Bezhaev Bismarck C.J.Date Cartesian product CASE cast CHAR CHARINDEX classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CTE data type conversion database schema DATEDIFF DATEPART DATETIME DELETE DISTINCT duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 exercise 37 exercise 39 exercise 46 exercise 54 exercise 55 exercise 56 exercise 57 exercise 7 exercise 70 exercise 8 exercises EXISTS FROM FULL JOIN GROUP BY More tags

The book was updated
several days ago

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Getting summarizing values

Aggregate function to aggregate function page 1