Aggregate Functions

SQL Aggregate Functions

SQL aggregate functions are built-in functions that perform calculations on a set of values and return a single value. They are often used with the GROUP BY clause to group the values into categories and apply the function to each category. Some of the most common SQL aggregate functions are:

- COUNT: returns the number of values in a set or the number of rows that match a condition.

- SUM: returns the sum of all values in a set.

- AVG: returns the average of all values in a set.

- MIN: returns the minimum value in a set.

- MAX: returns the maximum value in a set.

For example, to find the total number of employees and the average salary in each department, we can use the following query:

SELECT department_id, COUNT(*), AVG(salary)

FROM employees

GROUP BY department_id;

The result will look something like this:

department_id | count | avg

--------------|-------|-----

1             | 10    | 5000

2             | 15    | 6000

3             | 12    | 7000

4             | 8     | 8000

SQL aggregate functions can also be used with other clauses such as WHERE, HAVING, and ORDER BY to filter and sort the results. For example, to find the highest salary in each department that has more than 10 employees, we can use the following query:

SELECT department_id, MAX(salary)

FROM employees

WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10)

ORDER BY MAX(salary) DESC;

The result will look something like this:

department_id | max

--------------|-----

3             | 10000

2             | 9000

1             | 8000