MariaDB Tutorial / Group Results with GROUP BY and Filter Groups with HAVING

GROUP BY combines rows sharing a grouping key into group-level aggregates, and HAVING filters those groups. WHERE filters individual input rows before grouping, so moving a condition between WHERE and HAVING can change both the population and the result.

Define what one output row represents

A product query has one row per product; a category report has one row per category. Every selected nonaggregate value must have a defined meaning at that grain. Selecting a product name while grouping only by category does not establish which name should represent the group. Compare aggregate NULL/empty-set behavior in Summarize Rows with COUNT, SUM, AVG, MIN, and MAX and conditional aggregation within a group in Choose Conditional Values and Aggregates with CASE.

Check the MariaDB documentation for the syntax and conditions used here: group by.

Prepare category counts and values

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_23_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_23_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_p23_price CHECK (price >= 0),
  CONSTRAINT ck_p23_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_23_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.

The fixture has three stationery products, two household products and one electronics product. The first query includes all rows, even products with zero stock. Review the SELECT expressions and aliases guide when deciding what each output column means.

Create one aggregate row per category

SELECT category,COUNT(*) AS product_count,SUM(stock) AS total_stock,
 SUM(price*stock) AS inventory_value
FROM products_23_lab GROUP BY category ORDER BY category;

COUNT and SUM operate independently within each category. The table above translates the category meanings; the query returns their original Korean strings. Use explicit ORDER BY rather than depend on an observed grouping order. Collation can affect which text keys compare as the same group.

Filter aggregate values with HAVING

SELECT category,COUNT(*) AS product_count
FROM products_23_lab
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;

The remaining categories are stationery with three products and household with two. COUNT(*) describes the group and therefore belongs in HAVING for this condition. It is not an already-available row value that can be tested in WHERE. Using the aggregate expression directly makes the roles clear even where a result alias is also accepted.

Compare grouping after a WHERE filter

SELECT category,COUNT(*) AS in_stock_products
FROM products_23_lab WHERE stock > 0
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;

Filtering stock > 0 first removes the pen and cable. The remaining counts are stationery two and household two; there is no electronics group. HAVING cannot restore removed source rows, so total products and in-stock products must not be treated as the same metric.

A query selecting category, an ungrouped product_name and SUM(stock) is rejected with ONLY_FULL_GROUP_BY. Instead of disabling that mode, decide whether to group by the name too, changing the grain, or return only values defined for the category.

Review NULL groups and JOIN multiplicity

NULL grouping keys form a group together; do not apply WHERE’s ordinary NULL equality behavior as if it explained grouping. If aggregation follows a JOIN, inspect whether the join multiplied source rows first. For a small exercise, print each category’s source rows and reconcile them with the grouped output.

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_23_lab;