SQL aggregate functions are used to perform calculations on sets of values in a database table. These functions operate on a group of rows and return a single value as the result. Here are some commonly used SQL aggregate functions:
- COUNT() : Returns the number of rows or non-null values in a column. It can be used with wildcard (*) to count all rows in a table or with specific columns to count non-null values.
- SUM(): Calculates the sum of the values in a column. It is typically used with numeric data types.
- AVG(): Calculates the average of the values in a column. It is commonly used with numeric data types.
- MIN(): Returns the minimum value from a column, It is used to find the smallest value.
- MAX(): Returns the maximum value from a column. It is used to find the largest value.
- GROUP_CONCAT(): Concatenates values from multiple rows into a single string. It is commonly used with text or string data types
- HAVING() : Filtering condition used in conjunction with the GROUP BY clause. It allows you to specify conditions on the result of aggregate functions.
Here is an example of how these aggregate functions can be used in SQL:
SELECT COUNT(*) AS total_rows,
SUM(quantity) AS total_quantity,
AVG(price) AS average_price,
MIN(date) AS min_date,
MAX(date) AS max_date
FROM orders
WHERE status = 'completed';
this query calculates the total number of rows, the sum of the quantity, the average price, the minimum date, and the maximum date from the “order” table where status is ‘completed’.