SQL Aggregate Functions

Overview

Aggregate functions allow you to take multiple rows of input and produce a single output. These are especially useful for performing calculations on a set of rows to return meaningful summaries of the data.

Common Aggregate Functions

• “: Returns the average of a numeric column. Can be combined with ROUND() to specify precision.
• “: Returns the maximum value in a column.
• “: Returns the minimum value in a column.
• “: Returns the sum of all values in a numeric column.
• “: Counts the number of rows or non-null values in a column.

Usage Rules

1. Aggregate functions can only be used in the SELECT or HAVING clauses.
2. When combining aggregate functions with other columns, you must use a SQL group by statement to group the data.

Syntax Example

To call aggregate functions on a table:

SELECT MAX(x), MIN(y), AVG(z)
FROM table;

This query retrieves the maximum value of x, the minimum value of y, and the average of z.

Combining Aggregate Functions with GROUP BY

If you need to aggregate data while still displaying other columns:

SELECT column1, AVG(column2) AS avg_value
FROM table
GROUP BY column1;

In this case, the results will group rows by column1 and calculate the average of column2 for each group.

Notes

• Aggregate functions operate on entire columns or groups of rows.
• As long as an aggregate function returns a single value, it can be applied to multiple columns.
• When using AVG(), consider wrapping it in ROUND() if you need to control the number of decimal places:

  SELECT ROUND(AVG(column), 2) AS avg_value
  FROM table;