SQL Aggregate Functions

SQL Aggregate Functions

 

SQL Aggregate Functions



Summary: in this tutorial, you will learn about the SQL aggregate functions including AVG()COUNT()MIN()MAX(), and SUM().

An SQL aggregate function calculates on a set of values and returns a single value. For example, the average function ( AVG) takes a list of values and returns the average.

Because an aggregate function operates on a set of values, it is often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause divides the result set into groups of values and the aggregate function returns a single value for each group.

The following illustrates how the aggregate function is used with the GROUP BY clause:

SELECT c1, aggregate_function(c2) FROM table GROUP BY c1;

The following are the commonly used SQL aggregate functions:

  •  AVG() – returns the average of a set.
  •  COUNT() – returns the number of items in a set.
  •  MAX() – returns the maximum value in a set.
  •  MIN() – returns the minimum value in a set
  •  SUM() – returns the sum of all or distinct values in a set

Except for the COUNT() function, SQL aggregate functions ignore null.

You can use aggregate functions as expressions only in the following:

  • The select list of a SELECT the statement, either a subquery or an outer query.
  • HAVING clause

 AVG

The AVG() the function returns the average values in a set. The following illustrates the syntax of the AVG() function:

AVG( ALL | DISTINCT)

The ALL keyword instructs the AVG() function to calculate the average of all values while the DISTINCT keyword forces the function to operate on distinct values only. By default, the ALL the option is used.

The following example shows how to use the AVG() function to calculate the average salary of each department:

SELECT department_name, ROUND(AVG(salary), 0) avg_salary FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name;

 MIN

The MIN() the function returns the minimum value of a set. The following illustrates the syntax of the MIN() function:

MIN(column | expression)

For example, the following statement returns the minimum salary of the employees in each department:

SELECT department_name, MIN(salary) min_salary FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name;

 MAX

The MAX() the function returns the maximum value of a set. The MAX() the function has the following syntax:

MAX(column | expression)

For example, the following statement returns the highest salary of employees in each department:

SELECT department_name, MAX(salary) highest_salary FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name;

 COUNT

The COUNT() the function returns the number of items in a set. The following shows the syntax of the COUNT() function:

COUNT ( [ALL | DISTINCT] column | expression | *)

For example, the following example uses the COUNT(*) function to return the headcount of each department:

SELECT department_name, COUNT(*) headcount FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name;

 SUM

The SUM() the function returns the sum of all values. The following illustrates the syntax of the SUM() function:

SUM(ALL | DISTINCT column)

For example, the following statement returns the total salary of all employees in each department:

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;

In this tutorial, you have learned the most commonly used SQL aggregate functions including AVG()COUNT()MIN()MAX(), and SUM() functions.

Reactions

Post a Comment

0 Comments

close