Hiprup

What are aggregate functions in SQL?

Aggregate functions compute a single value from a set of rows. Often used with GROUP BY to summarize per-group; without grouping, they collapse the entire result set to one row.

  • COUNT — number of rows; COUNT(*) counts all, COUNT(col) ignores NULLs.

  • SUM — total of numeric values; ignores NULLs.

  • AVG — arithmetic mean.

  • MIN / MAX — smallest / largest value.

  • GROUP_CONCAT — concatenates values into a delimited string (MySQL-specific).

  • Ignore NULLs — all aggregates skip NULLs except COUNT(*).

SELECT 
  department,
  COUNT(*) AS total_employees,
  COUNT(bonus) AS employees_with_bonus,  -- Ignores NULLs
  SUM(salary) AS total_salary,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employee_names
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

COUNT(*) counts all rows. COUNT(bonus) counts only non-NULL bonuses. SUM/AVG/MIN/MAX compute salary statistics.

GROUP_CONCAT concatenates all names in each department into a comma-separated string. GROUP BY department creates one result row per department.

Know all 6: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT. COUNT(*) vs COUNT(column): * includes NULLs, column excludes NULLs.

GROUP_CONCAT is MySQL-specific (often asked). All aggregates ignore NULLs (except COUNT(*)).

What are aggregate functions in SQL? | Hiprup