What are aggregate functions?

What are aggregate functions? Asked with 20% probability

Aggregate functions in the context of database management and SQL — are special functions that perform calculations on a set of values and return a single result. They are often used to perform mathematical, statistical, and other operations with data in queries for analyzing and summarizing information from multiple records.

Main aggregate functions:

1. COUNT(): Counts the number of elements in a set. This function can be used to count the number of rows in a table or the number of rows matching certain criteria. Example: SELECT COUNT(*) FROM users; — counts the number of all rows in the users table.

2. SUM(): Sums numeric column values. Used to get the total sum of numeric data. Example: SELECT SUM(salary) FROM employees; — counts the total sum of all employees’ salaries.

3. AVG(): Calculates the average value of numeric data. This function is useful for finding the average value of a specific column. Example: SELECT AVG(price) FROM products; — calculates the average product price.

4. MIN() and MAX(): Return the minimum and maximum values in a column respectively. These functions are used to find the smallest and largest values. Example: SELECT MIN(age), MAX(age) FROM users; — finds the minimum and maximum age among users.

5. GROUP_CONCAT() (in MySQL) / STRING_AGG() (in PostgreSQL): Concatenates strings from a column, combining them into one string with a delimiter. Example (MySQL): SELECT GROUP_CONCAT(username SEPARATOR ', ') FROM users WHERE city = 'New York'; — combines usernames from New York with commas.

Aggregate functions are often used in conjunction with the GROUP BY operator, which allows grouping rows by one or more columns and applying aggregate functions to each group separately. This allows for more detailed data analysis. SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department; This query calculates the average salary for each department in the company.

Aggregate functions are a powerful tool for working with large data volumes, allowing you to quickly get summarized information about data, which is especially useful for reports and analytics.