Aggregate Functions

14 February 2012 By Nithya Vasudevan 436 views No Comment
0 Flares Twitter 0 Facebook 0 Google+ 0 0 Flares ×

Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:

AVG()Returns the average value
COUNT() Returns the number of rows
MAX() Returns the largest value
MIN()Returns the smallest value
SUM()Returns the sum

AVG() Function

The AVG() function returns the average value of a numeric column.

SQL AVG() Syntax:

SELECT AVG(column_name) FROM table_name

Now we want to find the average value of the “salary” fields.
We use the following SQL statement:

SELECT AVG(salary) from employee;

COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.

SQL COUNT(column_name) Syntax

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

MAX() Function

The MAX() function returns the largest value of the selected column.
SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name

MIN() Function

The MIN() function returns the smallest value of the selected column.
SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name

SUM() Function

The SUM() function returns the total sum of a numeric column.
SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name

The GROUP BY clause permits a WITH ROLLUP modifier that causes extra rows to be added to the summary output. These rows represent higher-level (or super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query.

Suppose if we want to find the number of male and female employees, we can use the COUNT() on ‘ssn’ with ‘gender’ column specified in GROUP BY clause. This will count the number of employees based on ‘gender’.

But we also want to find the total number of employees as well in the same query. Here is where, you can use the GROUP BY modifier, WITH ROLLUP as shown below.

The last row with ‘NULL’ value on ‘gender’ column gives the total number of employees.

Tags: , , , , , ,