What are the aggregate functions in sql

Aggregation functions in SQL are used to calculate and return a single value for a set of rows. Common aggregation functions include: Numeric aggregation functions: COUNT(), SUM(), AVG(), MIN(), MAX() Row set aggregation functions: GROUP_CONCAT(), FIRST(), LAST() Statistical aggregation functions: STDDEV (), VARIANCE() optional aggregate functions: COUNT(DISTINCT), TOP(N)

Aggregation functions in SQL

Aggregation A function is a function that performs a calculation on a set of rows and returns a single value. Common aggregate functions in SQL include:

Numeric aggregate functions:

• COUNT(): Counts the number of rows in a group, including non-NULL values.
• SUM(): Sums all non-NULL values ​​for a column in a group.
• AVG(): Calculates the average of the non-NULL values ​​of a column in a group.
• MIN(): Returns the minimum value of a column in the group.
• MAX(): Returns the maximum value of a column in the group.

Row set aggregate function:

• GROUP_CONCAT(): Concatenates all the values ​​​​of a column in the group into a string and specifies separator.
• FIRST(): Returns the first non-NULL value for a column in the group.
• LAST(): Returns the last non-NULL value for a column in the group.

Statistical aggregation function:

• STDDEV(): Calculate the standard deviation of a column value in a group.
• VARIANCE(): Calculates the variance of a column value in a group.

Optional aggregate function:

• COUNT(DISTINCT): Counts the number of unique values ​​for a column in a group.
• TOP(N): Returns the highest or lowest value of the specified number in the group.

Usage of aggregate functions:

Aggregation functions are typically used with the SQL GROUP BY clause, which groups data to apply aggregate calculations.

For example:

<code class="sql">SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;</code>

This query will group the employees table based on the department_id column and count the number of employees in each department.

The above is the detailed content of What are the aggregate functions in sql. For more information, please follow other related articles on the PHP Chinese website!