MariaDB Tutorial / Build and Reconcile Monthly Sales, Customer Totals, and Unsold Products

Monthly sales, customer purchases and unsold products use different result grains even when they read the same data. Define paid status, amount scope and the meaning of “unsold,” then reconcile monthly and customer totals against the same overall goods amount.

Write one shared definition for each report metric

Here sales means quantity × purchase-time unit price from paid order items; shipping and canceled orders are excluded. This is a reporting exercise rather than a complete accounting revenue, refund or tax policy. An unsold product is a registered product that has never appeared in a paid order. Check grouping criteria in Group Results with GROUP BY and Filter Groups with HAVING and reconcile JOIN multiplication with the small result sets in Avoid Duplicate Totals When Joining Multiple Tables.

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

Prepare a small fixture that can be reconciled by hand

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 c42_customers, c42_products, c42_orders, c42_items. 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 c42_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c42_order_customer FOREIGN KEY (customer_id)
  REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c42_item_order FOREIGN KEY (order_id)
  REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
  REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c42_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Paid goods are 11500 for order 101, 8000 for 102 and 19500 for 103: 39000 overall. Canceled order 104’s 1500 is excluded. Each article’s independent names keep earlier transaction experiments from changing this baseline.

Group by year-month and count orders at the correct grain

SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
 COUNT(DISTINCT o.order_id) AS paid_orders,
 SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;

The detail join has five paid item rows but only three orders, so the order metric uses COUNT(DISTINCT order_id). Grouping only by month number could combine the same month across years. This query does not automatically generate zero rows for missing months; that requires an explicit calendar population.

Retain nonbuyers in customer totals

SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

Customer totals are 19500.00, 19500.00, 0.00 and 0.00 for IDs 1 through 4. LEFT JOIN preserves customers and CASE includes only paid items. Moving the paid condition into WHERE can remove the no-order population, so maintain the filter placement required by the metric.

Do not sum order-level shipping directly across these detail rows: it can repeat once per item. This report sums only item amounts. Additional order-grain charges need a separate order-level calculation or preaggregation.

Find products with no paid sale history

SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
 WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;

Only product 4, the lamp, qualifies. NOT EXISTS checks whether any related paid order item exists. A product appearing only in canceled orders would still be unsold under this definition. A requirement based on all statuses would need a different condition.

Cross-check totals, omissions and time boundaries

The two monthly amounts sum to 39000, and so do the four customer amounts. If they differ, compare primary keys, period boundaries, status and unit-price definitions, then inspect join multiplication and filtering after LEFT JOIN. One plausible grand total does not establish that every report is correct.

Production reports also require a consistent observation time, time-zone policy and cancellation/refund handling. These fixed DATETIME inputs keep boundaries stable across execution dates. Parameterize actual requested periods instead of concatenating them into SQL strings.

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.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;