SQL aggregate functions are used to perform calculations on sets of values and return a single result. They operate on a group of rows and return a single value based on the values in the specified column(s). Here are some commonly used SQL aggregate functions:
- COUNT(): Returns the number of rows that match a specific condition or the total number of rows in a table.
Example:
SELECT COUNT(*) FROM employees;
- SUM(): Calculates the sum of the values in a column.
Example:
SELECT SUM(sales_amount) FROM sales;
- AVG(): Computes the average value of a column.
Example:
SELECT AVG(price) FROM products;
- MAX(): Finds the maximum value in a column.
Example:
SELECT MAX(salary) FROM employees;
- MIN(): Retrieves the minimum value in a column.
Example:
SELECT MIN(quantity) FROM inventory;
- GROUP_CONCAT(): Concatenates the values from a column into a single string, grouped by a specified column.
Example:
SELECT department, GROUP_CONCAT(employee_name) FROM employees GROUP BY department;
- HAVING(): Filters the results of a query based on a condition after using GROUP BY.
Example:
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
These aggregate functions can be used in conjunction with the SELECT statement to perform calculations and summarize data in a database.