back go forward

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
     ) X
WHERE 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_price
FROM Product P JOIN PC ON P.model=PC.model 
GROUP BY maker
HAVING 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_price
FROM 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

PostgreSQL inner join HAVING AVG MAX aggregate functions ALL common table expressions CTE

Previous [The sorting and NULL values]

[Product of column values] Next

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Getting summarizing values

Aggregate function to aggregate function page 3