SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, and AVG

Original photorealistic editorial image of an older male database engineer inspecting SQL aggregate queries and grouped results across two monitors in a server-room workspace, with the approved neon-green BitcoinVersus.tech wordmark on a tight black highlight at bottom left.

SQL aggregate functions turn collections of database rows into counts, totals, averages and other summaries. The GROUP BY clause collects rows sharing one or more values, while HAVING can select groups according to aggregate results. Together, those features help answer questions such as how many devices are assigned to each facility or which fictional locations exceed a selected sum. The official SQLite SELECT reference defines the grouping and filtering rules.

The subject builds on SQL joins and unmatched records. Earlier lessons explain relational tables, SELECT queries, row filtering with WHERE, and sorting with ORDER BY. Our examples use an invented inventory rather than live equipment telemetry or a manufacturer dataset.

Elementary Overview

Imagine a box of receipts from several workshops. Counting the entire box tells you how many receipts exist. Sorting receipts into one pile per workshop lets you count or total each pile separately. SQL GROUP BY performs the electronic equivalent of sorting into piles. HAVING can then return only piles meeting a condition, while WHERE first decides which individual receipts are eligible for inclusion.

Learning Objectives

  • Identify the different meanings of COUNT(*) and COUNT(column).
  • Calculate group totals using SUM, AVG, MIN, and MAX.
  • Explain how NULL values affect aggregates and grouping.
  • Compare WHERE filtering before aggregation with HAVING filtering after grouping.
  • Run a self-contained SQLite exercise and reproduce 28 local automated checks.

RuchiCodes, “SQL GROUP BY and HAVING Clause Explained,” published March 15, 2026. A publicly identified walkthrough of grouping, COUNT, SUM, AVG, and filtering groups. Original educational video. Embed presence is distinct from verified player playback.

COUNT, SUM, AVG, MIN, and MAX

COUNT(*) counts rows, even when a counted row contains missing information. COUNT(watts) counts only rows whose watts value is not NULL. In the training dataset, 12 equipment rows exist but only 10 have known watt values, so those two counts differ. COUNT(DISTINCT device_type) counts unique non-NULL device types rather than all rows.

SUM(watts) adds known watt values while excluding NULL. AVG(watts) divides the non-NULL sum by the number of known values, not by the entire row count. MIN and MAX identify smallest and largest non-NULL entries. The SQLite aggregate-function reference documents each operation, including an important empty-input distinction: SUM returns NULL when no non-NULL values exist, while SQLite’s nonstandard total() function returns 0.0. Other database engines may differ in extensions and numeric behavior.

GROUP BY Creates One Result Per Group

A query grouping by facility returns one row for each distinct facility value. North has five device records, South has three, West has three, and one unassigned record has a NULL facility. SQLite groups the missing facility value as a separate group, rather than silently discarding the row. For North, four of five watt values are known, their sum is 1,600 W, and their average is 400 W. Those figures are synthetic teaching values, not operational electrical measurements.

Adding a second grouping column, as in GROUP BY facility, device_type, makes a separate group for each distinct combination. Only grouped columns and aggregate expressions should be selected in portable grouped queries. SQLite permits certain ungrouped bare columns, but their values may come from arbitrarily selected input rows. Avoid relying on such behavior for cross-database correctness. Use ORDER BY when a deterministic display order matters.

Official SQLite database logo showing a blue quill and the SQLite lettering
Official SQLite project logo, obtained directly from sqlite.org. The sample below runs against SQLite rather than a production database.

WHERE Filters Rows, HAVING Filters Groups

A WHERE watts >= 250 condition removes low or unknown watt values before the aggregation. North retains three qualifying records totaling 1,450 W, South retains two totaling 880 W, and West retains one totaling 400 W. A different condition, HAVING SUM(watts) >= 1000, examines each already-formed group and returns North and South based on the full group’s total. A filtering condition on individual rows does not generally answer the same question as one on aggregated groups.

SQL’s logical processing model places row filtering before grouping and group filtering afterward; an engine may use different physical execution strategies that preserve those semantics. HAVING may also be valid without an explicit GROUP BY in aggregate queries, depending on the SQL dialect. The central rule remains: a condition depending on SUM or COUNT belongs in a grouped result filter rather than an ordinary row-level WHERE expression.

freeCodeCamp.org, “Intuitive SQL for Data Analytics,” a verified long-form course. The section on GROUP BY and aggregations begins near 5:15:39, offering an extended introduction to grouped queries. Original course video. Its database environment and examples may differ from the SQLite exercise.

Complete SQLite Practice Dataset

Save the following source as ossql-007-aggregate-demo.sql. The file builds an in-memory-friendly exercise database with fictional North, South, West and unassigned equipment records; unknown watt values are deliberately represented as NULL. It then runs four query demonstrations. Execute the SQL in the SQLite command-line shell or use the separate Python verification harness preserved with the lesson archive:

-- All equipment entries and wattage values are fictional.
DROP TABLE IF EXISTS equipment;
CREATE TABLE equipment (
    device_id INTEGER PRIMARY KEY,
    facility TEXT,
    device_type TEXT NOT NULL,
    watts INTEGER CHECK (watts >= 0)
);
INSERT INTO equipment (device_id, facility, device_type, watts) VALUES
 (1, 'North', 'Router', 150),
 (2, 'North', 'Switch', 300),
 (3, 'North', 'Server', 500),
 (4, 'North', 'Server', NULL),
 (5, 'South', 'Router', 120),
 (6, 'South', 'Switch', 280),
 (7, 'South', 'Server', 600),
 (8, 'West', 'Switch', 200),
 (9, 'West', 'Server', 400),
 (10, 'West', 'Server', NULL),
 (11, NULL, 'Spare', 100),
 (12, 'North', 'Server', 650);

SELECT facility,
       COUNT(*) AS device_rows,
       COUNT(watts) AS known_watt_values,
       SUM(watts) AS sum_watts,
       ROUND(AVG(watts), 2) AS avg_watts
FROM equipment
GROUP BY facility
ORDER BY facility IS NULL, facility;

SELECT facility, COUNT(*) AS qualifying_rows, SUM(watts) AS sum_watts
FROM equipment
WHERE watts >= 250
GROUP BY facility
ORDER BY facility;

SELECT facility, COUNT(*) AS device_rows, SUM(watts) AS sum_watts
FROM equipment
WHERE facility IS NOT NULL
GROUP BY facility
HAVING SUM(watts) >= 1000
ORDER BY facility;

SELECT facility, device_type, COUNT(*) AS device_rows
FROM equipment
WHERE facility IS NOT NULL
GROUP BY facility, device_type
ORDER BY facility, device_type;

After executing the sample, the first grouped query should return four groups. North has 5 rows and 1,600 W, South has 3 rows and 1,000 W, West has 3 rows and 600 W, and the unassigned group has 1 row and 100 W. The respective known-value averages are 400.00 W, 333.33 W, 300.00 W and 100.00 W. Notice that the displayed sums do not imply real equipment draw, energy consumption, or capacity.

The query with HAVING SUM(watts) >= 1000 returns the two named facilities North and South. For multi-column grouping, North contains three records with Server as their device type, while West contains two. Results were checked in SQLite 3.46.1 through Python’s built-in sqlite3 module.

Verification, Edge Cases, and Query Safety

The companion Python harness includes 28 passing checks, covering row totals, known and missing values, overall wattage, grouped averages, distinct categories, two-column groups, WHERE, HAVING, and empty-result behavior. A group with three rows may contain fewer than three known watt values. Accordingly, HAVING COUNT(*) >= 3 need not match HAVING COUNT(watts) >= 3. The sample demonstrates that difference.

Database queries involving user-supplied values should use bound parameters instead of concatenating raw input strings. Aggregate values are also affected by missing records, duplicate rows introduced by joins, data quality and integer overflow. When aggregating after a join, verify that row multiplicity has not inflated counts or sums. No performance benchmark, live infrastructure deployment, or production query-plan optimization was conducted for the example.

Knowledge Check

  • Why can COUNT(*) exceed COUNT(watts)? The second expression ignores rows with NULL watt values.
  • Where does SUM(watts) >= 1000 belong when filtering whole facility groups? In HAVING.
  • Why does North’s average divide by four rather than five? Only four North rows have a known watt value.
  • How should multiple dimensions be grouped? List the dimensions together, for example GROUP BY facility, device_type.

References

Elementary Conclusion

SQL aggregation turns many records into useful summaries. COUNT measures rows or known values, SUM adds values, and AVG calculates a mean from non-NULL inputs. GROUP BY organizes those calculations by category, and HAVING can retain only the groups that satisfy a stated rule. Correct handling of missing values and duplicated records helps keep reports reliable.

Editor’s Note: The internal lesson identifier OSSQL.007 follows the published SQL joins lesson in the 43-subject rotation. The cover is original AI-generated photorealistic editorial artwork of a fictional database engineer, not a documentary record of a working data center. The in-body SQLite logo comes directly from the official project website. The inventory, nominal watt values and computed results are entirely synthetic. Twenty-eight local checks passed against SQLite 3.46.1. Two public educational videos were identified and embedded; third-party player playback and mobile viewport screenshots require separate testing. No numerical SmartChart was generated because the only available numeric dataset is fictional teaching material, not a real measured comparison.

Original BitcoinVersus.Tech optional 5,000-satoshi donation QR code
Optional BitcoinVersus.Tech 5,000-satoshi donation QR. Verify the recipient and amount in your wallet before sending funds.

On mobile, show the original QR on another screen or use a compatible wallet’s saved-image scanner. Confirm the recipient and requested 5,000 satoshis before authorizing any payment.

We volunteer daily to ensure the credibility of information on BitcoinVersus.Tech is Verifiably True. If you would like to support our work and help further secure the integrity of our research initiatives, please scan the QR code above.

BitcoinVersus.Tech provides programming education and does not certify real-world electrical measurements, secure production database systems, or operational procedures. Validate code and data against the intended environment before relying on the output.

Leave a Reply