Combining Aggregate Functions


All the examples of aggregate functions used thus far have involved a single function. But actually, SELECT statements may contain as few or as many aggregate functions as needed. Look at this example:

Input

SELECT COUNT(*) AS num_items,        MIN(prod_price) AS price_min,        MAX(prod_price) AS price_max,        AVG(prod_price) AS price_avg FROM products;

Output

+-----------+-----------+-----------+-----------+ | num_items | price_min | price_max | price_avg | +-----------+-----------+-----------+-----------+ |        14 |      2.50 |     55.00 | 16.133571 | +-----------+-----------+-----------+-----------+

Analysis

Here a single SELECT statement performs four aggregate calculations in one step and returns four values (the number of items in the products table; and the highest, lowest, and average product prices).

Tip

Naming Aliases When specifying alias names to contain the results of an aggregate function, try to not use the name of an actual column in the table. Although there is nothing actually illegal about doing so, using unique names makes your SQL easier to understand and work with (and troubleshoot in the future).





MySQL Crash Course
MySQL Crash Course
ISBN: 0672327120
EAN: 2147483647
Year: 2004
Pages: 214
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net