Part III — SQL: Structured Query Language

Chapter 11. SQL Functions and Aggregate Queries

Single-row queries answer single-row questions. Reports answer population questions — "how many," "on average," "by department," "per year" — and that is where SQL stops being a lookup tool and becomes an analysis tool. This chapter adds the two layers that make it one: functions (string, numeric, date, conversion, and CASE) that transform values row by row, and aggregates (COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING) that summarize whole tables.

The chapter's quiet theme is NULL, one last time. Aggregates skip NULLs silently, COALESCE papers over them, and CASE makes missing data visible — a report writer who cannot predict NULL behavior cannot predict the dean's report.

After studying this chapter you will be able to:

• Apply string, numeric, and date/time functions, with each platform's dialect notes.
• Convert values safely with CAST and understand conversion strictness differences.
• Write searched and simple CASE expressions, including grade-to-points mapping.
• Use all five aggregates and predict their NULL and empty-set behavior.
• Group rows with GROUP BY and obey the single-value rule.
• Filter groups with HAVING and articulate WHERE-versus-HAVING precisely.
• Handle NULLs in reports with COALESCE and NULLIF.
• Assemble statistical summaries and business reports.

11.1 String functions

The everyday set, portable across both platforms in these forms:

SELECT full_name,
       UPPER(full_name)                     AS shouting,
       LENGTH(full_name)                    AS chars,
       SUBSTRING(full_name, 1, 4)           AS first_four,
       POSITION('M' IN full_name)           AS first_m
FROM   student
WHERE  student_id = 21300003;

  full_name  |    shouting    | chars | first_four | first_m
-------------+----------------+-------+------------+---------
 Arif Mahmud | ARIF MAHMUD    |    11 | Arif       |       6

Two dialect notes worth memorizing. Concatenation: standard/PostgreSQL spells 'a' || 'b', MySQL spells CONCAT('a', 'b') — production code that must run on both writes CONCAT everywhere. Length: PostgreSQL's LENGTH counts characters; MySQL's LENGTH counts bytes while CHAR_LENGTH counts characters — invisible in ASCII, decisive once names carry Bengali or other non-Latin scripts, where a 6-character name can be 12–18 bytes. Chapter 15's platform chapters extend the function inventory; this set covers daily reporting.

11.2 Numeric functions

ROUND, TRUNC, ABS, MOD, CEIL, FLOOR, POWER — the math layer of every report:

SELECT ROUND(3.4754, 2)   AS rounded,    -- 3.48
       TRUNC(3.4754, 2)  AS truncated,  -- 3.47
       MOD(104, 5)       AS remainder,   -- 4
       CEIL(3.14)        AS up,          -- 4
       FLOOR(3.99)       AS down,        -- 3
       ABS(-3.50)        AS absolute;    -- 3.50

Two notes. ROUND rounds half away from zero for exact numerics (3.475 → 3.48); floating-point values round by IEEE rules and can surprise — one more reason money is NUMERIC, never REAL (Chapter 9). Integer division differs by spelling: PostgreSQL / on integers truncates (104 / 5 = 20), MySQL provides the explicit DIV operator (104 DIV 5 = 20) and / yields a decimal — a classic portability trap inside expressions; cast operands when the difference matters.

The aggregate preview that motivates the whole chapter: ROUND(AVG(gpa), 2) over the student table returns 3.48 — the university's average GPA. Unrounded, the two platforms print differently (Section 11.6), which is why the rounding layer matters for any report a human reads.

11.3 Date and time functions

Dates are values (Chapter 9), so they have functions:

SELECT full_name,
       hire_date,
       EXTRACT(YEAR FROM hire_date)              AS hired,
       hire_date + INTERVAL '1 year'             AS first_review
FROM   instructor
ORDER  BY hire_date;

     full_name      | hire_date  | hired | first_review
--------------------+------------+-------+--------------
 Nazmul Chowdhury   | 2015-03-10 |  2015 | 2016-03-10
 Mahmudul Islam     | 2016-09-05 |  2016 | 2017-09-05
 Ahmed Kabir        | 2018-01-15 |  2018 | 2019-01-15
 Sharmin Ahmed      | 2019-02-20 |  2019 | 2020-02-20
 Farhana Rahman     | 2020-08-01 | 2020 | 2021-08-01
 Tahmina Karim      | 2021-01-10 |  2021 | 2022-01-10

EXTRACT(field FROM ts) is standard and both platforms honor it (YEAR, MONTH, DAY, HOUR, ...; MySQL also offers the shorthand functions YEAR(d), MONTH(d)). Date arithmetic is INTERVAL-based and standard in PostgreSQL; MySQL spells the same idea with DATE_ADD(hire_date, INTERVAL 1 YEAR) and its DATEDIFF(a, b) counts days between dates, while PostgreSQL reaches for AGE(a, b) to produce an interval ("6 years 2 mons 8 days"). "Now" is CURRENT_DATE / CURRENT_TIMESTAMP (both platforms, standard) — this book's now is Fall 2026, so chapter listings pin literal dates rather than wall-clock functions where reproducible outputs matter.

11.4 Type conversion and casting

CAST(value AS type) is the standard, spelled everywhere:

SELECT CAST('2026-01-15' AS DATE)               AS as_date,
       CAST(gpa AS DECIMAL(4,2))                AS gpa_wide,
       CAST(student_id AS CHAR(8))              AS id_text
FROM   student
WHERE  student_id = 21300003;

PostgreSQL adds the historical shorthand value::type (gpa::text) — terse, beloved, nonstandard. Casting matters at the seams: dates arrive as strings from files and forms; report builders need numbers as text (CONCAT); comparisons between unlike types need alignment. The dialect difference is strictness: PostgreSQL refuses nonsense ('abc'::integer errors immediately), MySQL's classic mode coerces 'abc' to 0 with a warning — which is why a MySQL import that silently zeroed a date column is a war story every team has. Modern MySQL strict modes (the default since 5.7) close most of that gap. Professional rule: cast at boundaries explicitly, and never rely on implicit string-to-number coercion in a WHERE clause — it bypasses indexes (Chapter 19) as well as common sense.

11.5 Conditional expressions using CASE

CASE is SQL's if/else — an expression, usable wherever a value is: select lists, WHERE, ORDER BY (Chapter 13 puts it inside aggregates). The searched form tests arbitrary conditions; the simple form compares one expression against values:

SELECT student_id,
       grade,
       CASE grade
         WHEN 'A'  THEN 4.0
         WHEN 'A-' THEN 3.7
         WHEN 'B+' THEN 3.3
         WHEN 'B'  THEN 3.0
         WHEN 'C+' THEN 2.3
         ELSE NULL
       END AS grade_points
FROM   enrollment
WHERE  section_id = 8 AND grade IS NOT NULL;

 student_id | grade | grade_points
------------+-------+--------------
   21100001 | B+    |          3.3
   21100002 | A     |          4.0
   21100003 | A-    |          3.7
   21100005 | C+    |          2.3

Section 8's four graded enrollments become grade points — the mapping the whole book's GPA scale uses. Two disciplines: always write the ELSE (its default is NULL — silent missing values instead of loud errors), and put NULL handling in the searched form when the branch conditions need it:

SELECT full_name,
       CASE WHEN gpa >= 3.70 THEN 'Excellent'
            WHEN gpa >= 3.30 THEN 'Good'
            WHEN gpa >= 3.00 THEN 'Satisfactory'
            WHEN gpa IS NULL THEN 'Not yet computed'
            ELSE 'At risk'
       END AS standing
FROM   student
ORDER  BY gpa DESC NULLS LAST;

The standing report turns Zara's NULL into a stated fact instead of a blank — reporting honesty at the expression level.

11.6 Aggregate functions: COUNT, SUM, AVG, MIN, and MAX

Aggregates collapse many rows into one value. The canonical facts:

An aggregate runs over the rows of its one FROM clause, so each table's summary is its own statement:

SELECT COUNT(*) AS students, COUNT(gpa) AS with_gpa,
       MAX(gpa) AS best, MIN(gpa) AS lowest
FROM   student;

 students | with_gpa | best | lowest
----------+----------+------+--------
       12 |       11 | 3.90 |   2.98

The NULL law of aggregates, all visible at once: COUNT(\*) counts rows (12); COUNT(col) counts non-NULL values (11); SUM/AVG/MIN/MAX ignore NULLs entirely — so MIN(gpa) is Farhan's 2.98, not NULL, because the 11 non-NULL values include 2.98. SUM(budget) over the five departments is 12800000.00; ROUND(AVG(salary), 2) over instructors is 109166.67. Two platform truths: unrounded AVG(gpa) prints as 3.4754545454545455 in PostgreSQL (full numeric precision) and 3.4755 in MySQL (DECIMAL averages round to 4 places) — round for humans, always; and an aggregate over an empty set returns one row: COUNT says 0, the others say NULL (SELECT AVG(gpa) FROM student WHERE major_dept_id = 99; → NULL) — the "why does my report show NULL instead of 0" question, answered in advance.

11.7 Grouping with GROUP BY

An aggregate alone returns one row; GROUP BY partitions the table and runs the aggregate per group:

SELECT major_dept_id,
       COUNT(*)           AS students,
       ROUND(AVG(gpa), 2) AS avg_gpa
FROM   student
GROUP  BY major_dept_id
ORDER  BY students DESC, major_dept_id;

 major_dept_id | students | avg_gpa
--------------+----------+---------
            1 |        4 |    3.66
            2 |        3 |    3.61
            3 |        2 |    3.10
            4 |        2 |    2.98
            5 |        1 |    3.60

One group per major, and note Mathematics (dept 4): two students, but average 2.98 — one student's GPA, because Zara Hossain's NULL is skipped by AVG. The group's row count (2) and its average's input count (1) disagree, and a good report writer knows why without checking. AVG(gpa) over an all-NULL group returns NULL (no 2026-only group exists here; the admission-year laboratory shows one).

The grammar rule that makes GROUP BY lawful: every non-aggregated item in the SELECT list must appear in the GROUP BY — the single-value rule: for each group, the query must produce exactly one row, and a bare non-grouped column could offer many values for one output slot. PostgreSQL enforces it always; MySQL enforces it with ONLY_FULL_GROUP_BY, the default since 5.7 — the historic lax behavior (silently picking an arbitrary value) is the bug that made the rule famous. Expressions may be grouped (GROUP BY EXTRACT(YEAR FROM hire_date)), and grouped queries accept WHERE — applied before grouping, so it filters which rows form groups at all.

11.8 Filtering groups with HAVING

HAVING filters groups, after aggregation; WHERE filters rows, before. The distinction is the section:

SELECT   section_id, COUNT(*) AS enrolled
FROM     enrollment
GROUP BY section_id
HAVING   COUNT(*) >= 3
ORDER BY enrolled DESC;

 section_id | enrolled
------------+----------
          8 |        4
         11 |        4
          1 |        3
         10 |        3

Four sections have three or more enrollments — MAT116's Fall 2024 section (8) and ENG105's current section (11) lead with four. The aggregate in HAVING is what WHERE cannot do: WHERE COUNT(*) >= 3 is a syntax error, because no single row has a count. Combined, the two filters read in execution order:

SELECT   semester, section_year, COUNT(*) AS sections
FROM     course_section
WHERE    section_year >= 2025           -- rows first
GROUP BY semester, section_year
HAVING   COUNT(*) >= 1                  -- groups second
ORDER BY section_year;

 semester | section_year | sections
----------+--------------+----------
 Spring   |         2025 |        5
 Spring   |         2026 |        1
 Fall     |         2026 |        3

WHERE removed the 2024 sections (4 rows), then grouping produced one row per (semester, year), then HAVING kept all three surviving groups. The professional reading order for any grouped query, worth saying aloud every time: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

11.9 NULL handling with COALESCE and related expressions

The report-writing NULL kit:

• COALESCE(a, b, ...) — first non-NULL argument: COALESCE(gpa, 0.00) gives Zara a reportable 0.00; COALESCE(room, 'TBA') turns unscheduled rooms into stated placeholders. The honesty rule from Section 11.5 applies: COALESCE makes reports readable, and it hides the difference between "0.00" and "no GPA" — prefer a CASE that says which (Section 11.5's standing report) whenever the distinction could mislead.
• NULLIF(a, b) — NULL when a = b, else a: the classic safe-division guard, SUM(credits) / NULLIF(COUNT(*), 0) — an empty group yields NULL instead of a division-by-zero error.
• Aggregates + COALESCE: COALESCE(AVG(gpa), 0) renders the empty-set NULL as 0 where a dashboard needs a number — with the same honesty caveat.
• FILTER (WHERE ...) — the standard's per-aggregate condition: COUNT(*) FILTER (WHERE grade IS NOT NULL) counts graded enrollments only, in one pass. PostgreSQL implements it; MySQL 8.0 does not, spelling the same idea as SUM(CASE WHEN grade IS NOT NULL THEN 1 ELSE 0 END) — the conditional aggregation pattern Chapter 13 develops fully.

11.10 Statistical summaries and business reports

The chapter's machinery assembles into reports. Three canonical examples, each with a lesson.

The grade distribution — GROUP BY over a filtered domain:

SELECT grade, COUNT(*) AS awarded
FROM   enrollment
WHERE  grade IS NOT NULL
GROUP  BY grade
ORDER  BY awarded DESC, grade;

 grade | awarded
-------+---------
 A     |       7
 A-    |       4
 B+    |       4
 B     |       4
 C+    |       1

Twenty graded enrollments; A leads with seven. WHERE grade IS NOT NULL before grouping is the NULL law applied — without it, an eighth group (NULL, 8) would appear in the dean's report.

The cohort study — one row per admission year, NULLs visible:

SELECT admission_year,
       COUNT(*)           AS students,
       ROUND(AVG(gpa), 2) AS avg_gpa,
       MAX(gpa)          AS best
FROM   student
GROUP  BY admission_year
ORDER  BY admission_year;

 admission_year | students | avg_gpa | best
----------------+----------+---------+------
           2021 |        3 |    3.68 | 3.88
           2022 |        4 |    3.35 | 3.70
           2023 |        3 |    3.52 | 3.90
           2025 |        1 |    3.25 | 3.25
           2026 |        1 |  (null) | (null)

The 2026 row is the whole chapter in one line: COUNT sees Zara (1), AVG and MAX see nothing (NULL) — and the correct report shows it rather than papering it over.

The department summary — grouping a joined table (the join is Chapter 12's subject; read this as a preview whose only new piece is the join):

SELECT d.dept_name,
       ROUND(AVG(i.salary), 2) AS avg_salary,
       COUNT(i.instructor_id)  AS instructors
FROM   department d
JOIN   instructor  i ON i.dept_id = d.dept_id
GROUP  BY d.dept_name
ORDER  BY avg_salary DESC;

              dept_name               | avg_salary | instructors
---------------------------------------+------------+-------------
 Electrical and Electronic Engineering | 130000.00  |           1
 Computer Science and Engineering      | 112500.00  |           2
 Mathematics                           | 110000.00  |           1
 Business Administration              |  98000.00  |           1
 English                               |  92000.00  |           1

Every department hosts exactly one instructor except CSE (two — hence 112500.00, the mean of 120000 and 105000). One warning before Chapter 12 makes it precise: aggregating over a join fans rows out, and an AVG computed over a fanned-out join counts some rows twice — always know your row multiplicity before you trust a joined average (Section 12.6 returns to exactly this trap). With that caution filed, the report-writing pattern is complete: filter (WHERE), group (GROUP BY), aggregate (COUNT/AVG/...), shape (CASE, ROUND, COALESCE), and order (ORDER BY) — the pipeline every dashboard, dean's report, and Chapter 25 warehouse query is built from.

Chapter Summary

• String functions (UPPER, LOWER, LENGTH, SUBSTRING, POSITION, TRIM, REPLACE, CONCAT) transform row values; CONCAT is the portable concatenation; LENGTH counts bytes in MySQL, characters elsewhere.
• Numeric functions round, truncate, take moduli, and ceiling/floor; exact numerics round half away from zero; integer division is a portability trap.
• Date functions: EXTRACT is standard (MySQL adds YEAR()/MONTH()), arithmetic is INTERVAL-based, AGE/DATEDIFF differ by platform.
• CAST converts explicitly; PostgreSQL is strict, classic MySQL coerced silently — cast at boundaries, never rely on implicit coercion.
• CASE (searched and simple) is an expression usable anywhere a value is; always write the ELSE.
• COUNT(*) counts rows, COUNT(col) non-NULLs, and SUM/AVG/MIN/MAX skip NULLs; aggregates over empty sets return COUNT 0 and NULL otherwise.
• GROUP BY obeys the single-value rule (non-aggregated SELECT items must be grouped); WHERE filters rows before grouping, HAVING filters groups after; the reading order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• COALESCE, NULLIF, and FILTER (or MySQL's CASE-based conditional aggregation) handle NULLs in reports — with the honesty rule: show absence as absence when it could mislead.
• Reports assemble the pipeline: grade distribution, cohort study (2026's NULL row), and department summary (with the joined-aggregate fan-out warning).

Key Terms

Laboratory Exercises

1. Run the Section 11.1 string-function listing on all four students whose names contain 'ah' (the LIKE query of Chapter 10), and add CONCAT(full_name, ' (', major_dept_id, ')'). Expected result: three rows — Nusrat Jahan, Arif Mahmud, Shahriar Islam — with lengths 12, 11, 14 and concatenated labels like 'Arif Mahmud (1)'.
2. Compute the university's average GPA three ways — raw AVG(gpa), ROUND(AVG(gpa), 2), and TRUNC(AVG(gpa), 2) — and record both platforms' raw forms. Expected results: PostgreSQL raw 3.4754545454545455, MySQL raw 3.4755; rounded 3.48; truncated 3.47.
3. List every instructor's hire year with EXTRACT(YEAR FROM hire_date), ordered, and add a hire_date + INTERVAL '5 years' (or DATE_ADD in MySQL) column. Expected result: 2015, 2016, 2018, 2019, 2020, 2021 — Nazmul, Mahmudul, Ahmed, Sharmin, Farhana, Tahmina — each with the five-year anniversary date.
4. Map section 1's grades to points with a simple CASE (include D and F branches even though unused), and predict the three rows before running. Expected result: Nusrat Jahan A → 4.0, Rakib Hasan B+ → 3.3, Arif Mahmud A → 4.0.
5. Write and run: majors with more than two students; sections with at least 3 enrollments; and the per-major average including dept 4's NULL-skipping effect. Expected results: CSE (4) and EEE (3); sections 1, 8, 10, and 11 (three or more enrollments each); avg 2.98 for Mathematics over 2 students — Zara's NULL skipped.
6. Assemble the cohort study of Section 11.10 with a standing CASE column added, then write the NULLIF-guarded SUM(total_credits) / NULLIF(COUNT(*), 0) per major and explain any NULL that appears. Expected result: the five cohort rows with standings; per-major mean credits — no NULL appears (every major has students); the guard matters for empty groups, which majors cannot be here.

Review Questions and Exercises

1. Why does CONCAT appear in production code more than ||, and what is the trade? Portability across MySQL; the trade is four extra characters versus PostgreSQL/standard fidelity — CONCAT works everywhere.
2. Predict LENGTH('Nusrat Jahan') and CHAR_LENGTH of the same in both platforms. 12 characters; CHAR_LENGTH 12 on both; LENGTH 12 in PostgreSQL and 12 bytes in MySQL for this ASCII name — the two diverge only on non-Latin text.
3. What do TRUNC(3.4754, 2), ROUND(3.4754, 2), and MOD(104, 5) return? 3.47; 3.48; 4.
4. Write the standard cast of the string '2026-10-09' to a date, and each platform's extras. CAST('2026-10-09' AS DATE); PostgreSQL also '2026-10-09'::date; MySQL accepts both plus implicit conversion under strict-mode rules.
5. Why must a CASE always carry its ELSE, and what is the default? The default ELSE is NULL — missing branches become silent NULLs; an explicit ELSE makes the unhandled case stated.
6. Explain the three COUNT behaviors with the student table's numbers. COUNT() = 12 (rows); COUNT(gpa) = 11 (non-NULL); COUNT(DISTINCT major_dept_id) = 5 (distinct non-NULL values).*
7. Why is Mathematics' average GPA 2.98 when the department has two students? AVG skips NULLs — Zara Hossain's NULL is excluded, leaving only Farhan Akter's 2.98 in the average.
8. State the single-value rule and name each platform's enforcement. Every non-aggregated SELECT item must be in GROUP BY; PostgreSQL enforces it structurally, MySQL via ONLY_FULL_GROUP_BY (default since 5.7).
9. Why is WHERE COUNT(*) >= 3 illegal and what is the fix? WHERE runs per row before grouping and no row has a count; move the condition to HAVING, which runs per group.
10. Give the execution order of a grouped query's clauses and trace the section-count example through it. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY: rows 2025+ kept, grouped per (semester, year), all three groups kept, selected, ordered.
11. What does SUM(credits) / NULLIF(COUNT(*), 0) guard against, and what does it yield on an empty set? Division by zero; NULL — the aggregate-empty result surfaces as NULL rather than an error (COALESCE can then dress it).
12. Why is the 2026 cohort's row the chapter's best teaching row, and what two honest renderings does the chapter offer for it? COUNT sees the student while AVG/MAX see nothing — NULL versus 0 disagreement made visible; renderings: show NULL with a standing CASE ('Not yet computed') or COALESCE to a stated default.

Mini-Project

Build the registrar's one-page dashboard, dashboard.sql, as five report queries over the canonical database, each with a header comment stating the question and the expected output pasted in as comments after a real run: (1) the grade distribution with a percentage column (count over a window-free formulation: ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) is Chapter 13 territory — instead use a fixed total of 20 with a comment noting the total, or compute percentages in the application); (2) the cohort study with standings; (3) the per-major summary (students, average GPA, mean credits with the NULLIF guard); (4) the per-department instructor summary (average salary, count); (5) a sections report — enrollments per section with a HAVING filter for sections at half capacity or more (compare against capacity, mind the fan-out rule). Every query must handle its NULLs deliberately and state in a comment where a NULL could appear and how the query renders it. Run the dashboard on both platforms and file both outputs — this file becomes the baseline that Chapter 19 will speed up and Chapter 25 will warehouse.