This lab covers SQL built-in aggregate functions, data grouping, and grouping with ROLLUP.

Aggregation Functions

1) Basics

SQL provides some built-in functions for performing data aggregation and grouping. The main ones are:

• COUNT() – the number of rows
• MAX() – the largest value
• MIN() – the smallest value
• SUM() – the sum
• AVG() – the average value
• VARIANCE() – The statistical variance : the variability from the average
• STDDEV() – The statistical standard deviation

Aggregate functions can be used as expressions only in the following:

• The select list of a SELECT statement
• A HAVING clause.

Basic syntax:

SELECT *aggregate_function*([DISTINCT] *expression*), ... FROM *tbl_name,...* [WHERE *where_condition*];

where expression is a column or an expression involving columns (like an arithmetic expression). expression cannot be however an aggregate function.

• Try the following query to obtain the lowest price:

SELECT MIN(price) AS "Lowest price"  FROM Product;