MariaDB Tutorial / Summarize Rows with COUNT, SUM, AVG, MIN, and MAX

COUNT, SUM, AVG, MIN and MAX summarize rows as counts, totals, averages and bounds. COUNT(*) counts rows while COUNT(column) counts non-NULL values; define the unit, denominator and missing-data policy before interpreting an aggregate.

Define what a row and a measurement represent

The number of product types, total units in stock, average quantity per product and total inventory value measure different things. Check whether a product appears once or repeatedly and whether a number represents units or money. Valid aggregate syntax can still compute the wrong business metric. Use Group Results with GROUP BY and Filter Groups with HAVING for separate group totals. After joining tables, check for duplicated amounts caused by row multiplication as in Avoid Duplicate Totals When Joining Multiple Tables.

Check the MariaDB documentation for the syntax and conditions used here: AVG, COUNT, SUM.

Prepare notes and stock with distinct missing states

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_22_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_22_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p22_price CHECK (price >= 0),
  CONSTRAINT ck_p22_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_22_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

Two of six notes are NULL and two are empty strings. Zero-stock products still exist as rows and belong in COUNT(*). The NULL handling guide helps explain why missing values can disappear from a value-based count.

Compare all aggregates in one result

SELECT COUNT(*) AS all_rows,COUNT(note) AS known_notes,
 SUM(stock) AS stock_total,AVG(stock) AS stock_average,
 MIN(price) AS lowest_price,MAX(price) AS highest_price,
 SUM(price*stock) AS inventory_total
FROM products_22_lab;

AVG(stock) divides the sum of known quantities by the number of non-NULL quantities. Zero contributes to both that count and the sum; NULL does not represent a confirmed zero. Preserve that distinction when interpreting averages.

Check aggregates when no rows match

SELECT COUNT(*),SUM(stock),AVG(stock),COALESCE(SUM(stock),0)
FROM products_22_lab WHERE price < 0;

The four results are 0, NULL, NULL and 0. COUNT reports zero rows, but SUM and AVG return NULL for this empty input. A zero display fallback for an empty total may be appropriate, while recording an unobserved average as a real measured zero is a different decision.

Apply per-row arithmetic before the intended aggregate

SUM(price*stock) values each product first and then adds those amounts. SUM(price)*SUM(stock) multiplies two unrelated totals and measures something else. Investigate the operation’s unit and grain before blaming a large result on decimal precision.

SELECT product_id,price,stock,price*stock AS row_value
FROM products_22_lab ORDER BY product_id;

MIN(price) and MAX(price) return price values; they do not automatically select the matching product names. Several products can share an extreme price. Use a defined filter or ordering when the desired result is a list of products, and do not attach an arbitrary nonaggregated name to an aggregate.

Reconcile source rows and inspect duplicate input

For a small fixture, read every primary key and reconcile sums by hand. Confirm that the original six products have not been duplicated, that NULL has not been silently converted to zero and that a JOIN has not multiplied rows before summation. Large totals also require a suitable result type and application numeric range.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_22_lab;