What are aggregate functions in SQL?

Question

Accepted Answer

Aggregate functions perform a calculation across a set of rows and return a single summary value. They are most often combined with GROUP BY to summarize rows within categories, but can also run over an entire result set with no grouping at all.

Key Points:
• COUNT() counts rows (or non-NULL values in a specific column).
• SUM() and AVG() total and average numeric columns respectively.
• MIN() and MAX() return the smallest and largest values in a column.
• GROUP_CONCAT() (MySQL) concatenates values from multiple rows into a single string per group.
• Aggregate functions ignore NULL values in their input column, except COUNT(*), which counts all rows regardless of NULLs.

Example:
To find the total and average salary per department, you group employees by department_id and apply SUM(salary) and AVG(salary) to each group in a single query.

Code Example:
```
SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
```

Interview Tip:
A concise interview answer is:

"Aggregate functions like COUNT, SUM, AVG, MIN, and MAX collapse multiple rows into a single summary value, typically per group when combined with GROUP BY. A key gotcha is that they ignore NULLs in their target column, except COUNT(*), which counts every row regardless of NULLs."