loading..
   English
00:02

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
  1. SELECT maker, avg_price
  2. FROM (SELECT maker, AVG(price) avg_price
  3. FROM Product P JOIN PC ON P.model=PC.model
  4. GROUP BY maker
  5. ) X
  6. WHERE avg_price >= ALL(SELECT AVG(price) avg_price
  7. FROM Product P JOIN PC ON P.model=PC.model
  8. GROUP BY maker
  9. );

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
  1. SELECT maker, avg_price
  2. FROM (SELECT maker, AVG(price) avg_price
  3. FROM Product P JOIN PC ON P.model=PC.model
  4. GROUP BY maker
  5. ) X JOIN
  6. (SELECT MAX(avg_price) max_price
  7. FROM (SELECT maker, AVG(price) avg_price
  8. FROM Product P JOIN PC ON P.model=PC.model
  9. GROUP BY maker
  10. ) X
  11. ) 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
  1. SELECT maker, AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model=PC.model
  3. GROUP BY maker
  4. HAVING AVG(price) >= ALL(SELECT AVG(price) avg_price
  5. FROM Product P JOIN PC ON P.model=PC.model
  6. GROUP BY maker
  7. );

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
  1. WITH cte(maker, avg_price)
  2. AS (
  3. SELECT maker, AVG(price) avg_price
  4. FROM Product P JOIN PC ON P.model=PC.model
  5. GROUP BY maker
  6. )
  7. SELECT *
  8. FROM cte
  9. WHERE avg_price>= ALL(SELECT avg_price
  10. FROM cte
  11. );

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.

Bookmark and Share
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.
Rambler's Top100