loading..
Русский    English
02:55

Getting summarizing values

How many PC models does a particular supplier produce? How the average price is defined for computers with the same specifications? The answers to these and other questions associated with some statistic information may be obtained by means of summarizing (aggregate) functions. The following aggregate functions are assumed as standard:

Function Description
COUNT(*) Returns the number of rows of the table.
COUNT Returns the number of values in the specified column.
SUM Returns the sum of values in the specified column.
AVG Returns the average value in the specified column.
MIN Returns the minimum value in the specified column.
MAX Returns the maximum value in the specified column.

All these functions return a single value. In so doing, the functions COUNT, MIN, and MAX are applicable to any data types, while the functions SUM and AVG are only used with numeric data types. The difference between the functions COUNT(*) and COUNT(<column name>) is that the second does not calculate NULL values (as do other aggregate functions).

Example 5.5.1

Find out the minimal and maximal prices for PCs:

Console
Execute
  1. SELECT MIN(price) AS Min_price, MAX(price) AS Max_price
  2. FROM PC;

The result is a single row containing the aggregate values:

Min_price Max_price
350.0 980.0

Example 5.5.2

Find out the number of available computers produced by the maker А:

Console
Execute
  1. SELECT COUNT(*) AS Qty
  2. FROM PC
  3. WHERE model IN(SELECT model
  4. FROM Product
  5. WHERE maker = 'A'
  6. );

As a result we get:

Qty
8

Example 5.5.3

If the number of different models produced by the maker A is needed, the query may be written as follows (taking into account the fact that each model in the Product table is shown once):

Console
Execute
  1. SELECT COUNT(model) AS Qty_model
  2. FROM Product
  3. WHERE maker = 'A';

Qty_model
7

Example 5.5.4

Find the number of available different PC models produced by maker A.

This query is similar to the preceding one for the total number of models produced by maker A. Now we need to find the number of different models in the PC table (available for sale).

To use only unique values in calculating the statistic, the parameter DISTINCT with an aggregate function argument may be used. ALL is another (default) parameter and assumes that all the column values returned (besides NULLs) are calculated. The statement

Console
Execute
  1. SELECT COUNT(DISTINCT model) AS Qty
  2. FROM PC
  3. WHERE model IN (SELECT model
  4. FROM Product
  5. WHERE maker = 'A'
  6. );

gives the following result:

Qty
2

If we need the number of PC models produced by each maker, we will need to use the GROUP BY clause, placed immediately after the WHERE clause, if any.

Suggested exercises: 10, 11, 12, 13, 14, 18, 24, 25, 26, 27, 28, 43, 51, 53, 54, 58, 61, 62, 75, 77, 79, 80, 81, 85, 86, 88, 92, 93, 94, 95, 96, 103, 109, 127, 129

Bookmark and Share
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100