Aggregate Functions

Aggregate functions collapse many rows into a single value.

Function	Returns
COUNT()	Number of rows
SUM()	Total
AVG()	Average
MIN() / MAX()	Smallest / largest
GROUP_CONCAT()	Values joined into one string
STDDEV() / VARIANCE()	Statistical spread

SELECT
  COUNT(*)        AS total_students,
  COUNT(email)    AS with_email,
  AVG(cgpa)       AS avg_cgpa,
  MAX(cgpa)       AS best,
  MIN(cgpa)       AS worst,
  SUM(credits)    AS total_credits,
  ROUND(STDDEV(cgpa), 3) AS spread
FROM students;

COUNT variations

COUNT(*)                  -- all rows, including those with NULLs
COUNT(column)             -- rows where column IS NOT NULL
COUNT(DISTINCT column)    -- distinct non-null values
COUNT(CASE WHEN cgpa > 3.5 THEN 1 END)   -- conditional count

COUNT(*) is the fastest of these on InnoDB because the optimizer can use the smallest available index.

GROUP_CONCAT

SELECT department,
       GROUP_CONCAT(name_en ORDER BY name_en SEPARATOR ', ') AS students
FROM students
GROUP BY department;

The result is truncated at group_concat_max_len (1024 bytes by default):

SET SESSION group_concat_max_len = 1000000;

Aggregates ignore NULL

Except for COUNT(*), every aggregate skips NULL. If half your cgpa values are null, AVG(cgpa) divides by the non-null count. To treat nulls as zero:

SELECT AVG(COALESCE(cgpa, 0)) FROM students;

Aggregates cannot be used in WHERE

Because WHERE runs before grouping, this fails:

-- ERROR
SELECT department FROM students WHERE AVG(cgpa) > 3.0 GROUP BY department;

-- correct
SELECT department FROM students GROUP BY department HAVING AVG(cgpa) > 3.0;

Combining with expressions

SELECT
  department,
  COUNT(*)                                        AS students,
  ROUND(AVG(cgpa), 2)                             AS avg_cgpa,
  ROUND(100.0 * SUM(cgpa >= 3.5) / COUNT(*), 1)   AS pct_excellent
FROM students
GROUP BY department;

In MySQL a boolean expression evaluates to 1 or 0, so SUM(cgpa >= 3.5) is a neat conditional count.