## 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:
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: The result is a single row containing the aggregate values:
Example 5.5.2 Find out the number of available computers produced by the maker А:
As a result we get:
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):
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
gives the following result:
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: |

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
date/time functions
DATEADD
DATEDIFF
DATENAME
DATEPART
DATETIME
DDL
DEFAULT
DEFAULT VALUES
DELETE
DISTINCT
DML
duplicates
equi-join
EXCEPT
exercise (-2)
exercise 19
More tags

exercise 23
exercise 32
exercise 37
exercise 39
exercise 46
exercise 54
exercise 55
exercise 56
exercise 57
exercise 7
exercise 70
exercise 8
exercises
EXISTS
FLOAT
FOREIGN KEY
FROM
FULL JOIN
GROUP BY
grouping
Guadalcanal
HAVING
head ships
IDENTITY
IN
income
inner join
INSERT
INTERSECT
IS NOT NULL
IS NULL
ISNULL
join operations
laptop
launched year
LEFT
LEFT OUTER JOIN
LEN
LIKE
LTRIM
MAX
MIN
mistakes
MySQL
NOT
NOT IN
NULL
NULLIF
number sequences
number-sequence generation
numbering
ON DELETE CASCADE
OR
Oracle
ORDER BY
outcome
Outcomes
outer joins
OVER
paging
Painting
PARTITION BY
Pass_in_trip
PATINDEX
PC
PIVOT
PostgreSQL
predicates
primary key
printer
Product
Ranking functions
recursive CTE
renaming columns
REPLACE
RIGHT
RIGHT JOIN
ROUND
rounding
ROW_NUMBER
ships
sorting
SQL Server
SQL Server 2012
SQL-92
sql-ex.ru
string functions
subquery
SUBSTRING
SUM
tables join
tips and solutions
Torus
Transact-SQL
Trip
TRUNCATE TABLE
type conversion
UNION
UNION ALL
UNKNOWN
UPDATE
varchar
WHERE
window functions
WITH
XML
XPath
XQuery

The book was updated

*month ago*