Database Management · Beginner level

Beginner level · Module 4 of 5 · 11 guided hours

Aggregation and Reporting Queries

Summarise data with aggregate functions and GROUP BY, filter groups with HAVING and build report queries that answer management questions.

Learning objectives

  • Use COUNT, SUM, AVG, MIN and MAX
  • Group rows and filter groups
  • Build complete report queries with CASE and dates

Topics

  • aggregate functions
  • GROUP BY
  • HAVING vs WHERE
  • CASE expressions
  • date functions for months
  • report layout

Lessons

  1. Lesson 1 Aggregate Functions

    Count, total, average and find the extremes of data with aggregate functions.

    COUNT(*) vs COUNT(column)SUM and AVGMIN and MAXNULL in aggregates

  2. Lesson 2 GROUP BY and HAVING

    Summarise data per group and filter the groups.

    GROUP BYone row per groupHAVINGWHERE before grouping

  3. Lesson 3 Building Report Queries

    Combine joins, grouping, CASE and date functions into report queries that answer management questions.

    CASE WHENgrouping by monthreport columnschecking results

Practical activity

Produce the bursar's monthly report: collections per month, balances per class and the number of students in each payment status.

Expected outcomes

  • Accurate summary reports
  • Queries that management can use directly

Assessment

Report practical: five summary queries checked against hand calculations on a small data set.

Free taster lesson Aggregation & Reporting Read a free self-study lesson on this topic in the Free Course Library.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.