01:32

# Aggregate function to aggregate function page 3

We give below some standard solutions of the exercise.

1. Use predicate ALL in  WHERE clause â ïðåäëîæåíèè WHERE

Console
Execute
`SELECT maker, avg_price FROM (SELECT maker, AVG(price) avg_price      FROM Product P JOIN PC ON P.model=PC.model       GROUP BY maker     ) XWHERE avg_price >= ALL(SELECT AVG(price) avg_price                       FROM Product P JOIN PC ON P.model=PC.model                        GROUP BY maker                      );`

In natural language, this query sound like follows: "Find makers, for which the average price of PCs no less than the average prices for each of the makers of PCs".

2.  Use Inner joins

Console
Execute
`SELECT maker, avg_price FROM (SELECT maker, AVG(price) avg_price      FROM Product P JOIN PC ON P.model=PC.model       GROUP BY maker      ) X JOIN       (SELECT MAX(avg_price) max_price        FROM (SELECT maker, AVG(price) avg_price             FROM Product P JOIN PC ON P.model=PC.model              GROUP BY maker             ) X       ) Y ON avg_price = max_price;`

Here we join a subquery that defines the makers and the average prices on their PC, with a subquery, which defines the maximum average price. The join is performed by the condition of equality of the average price of the first subquery with a maximum price of the second.

3. Use predicate ALL in HAVING clause

Console
Execute
`SELECT maker, AVG(price) avg_priceFROM Product P JOIN PC ON P.model=PC.model GROUP BY makerHAVING AVG(price) >= ALL(SELECT AVG(price) avg_price                          FROM Product P JOIN PC ON P.model=PC.model                          GROUP BY maker                        );`

This solution differs from the first version of the lack of "extra" request, which had to write only then to be able to use the alias avg_price in WHERE clause (see  clause order for SELECT); on the other hand, using aggregate function in WHERE clause are also prohibited by the rules of language.

All of the standard solutions seem heavy, though, and will work in almost all databases. This cumbersome due to repetition in the code is actually one and the same query. However, common table expressions –  CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE, which were introduced in recent versions of the standard, allow repeatedly use a one formulated query. For example, solutions 1 and 3 using CTE can be written in the form:

Console
Execute
`WITH cte(maker, avg_price)AS (SELECT maker, AVG(price) avg_priceFROM Product P JOIN PC ON P.model=PC.model GROUP BY maker   )SELECT *FROM cte WHERE  avg_price>= ALL(SELECT avg_price                        FROM cte                       );`

Note that the maintenance of the common table expressions first appeared in  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2005 and PostgreSQL 8.4.

 Pages 1 2 3