MariaDB Tutorial / Calculate, Round, and Convert Numbers with ROUND and CAST

ROUND rounds a numeric value to a specified position, while CAST converts a value to an explicit type. For money, choose an exact DECIMAL representation and a defined rounding stage, then inspect the original input and any conversion warnings as well as the result.

Separate arithmetic, rounding and display formatting

Calculating a ten-percent discount, rounding to two decimal places and displaying a grouping comma are separate operations. Keep numeric values for arithmetic and format them for display rather than convert formatted text back into numbers for totals. Before calculating, distinguish exact and approximate types using Choose Numeric Types: Integers, DECIMAL, FLOAT, and DOUBLE; Choose Conditional Values and Aggregates with CASE can select a calculated value according to a condition.

Check the MariaDB documentation for the syntax and conditions used here: ROUND, CAST.

Calculate discounts using exact 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_20_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_20_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_p20_price CHECK (price >= 0),
  CONSTRAINT ck_p20_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_20_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.

SELECT product_id, price, ROUND(price * 0.9,2) AS discounted_price
FROM products_20_lab ORDER BY product_id;

The notebook becomes 3150.00, the pen 1350.00 and the lamp 22500.00. The stored price is DECIMAL and 0.9 is an exact decimal literal. The integer, DECIMAL and floating-point comparison explains why approximate inputs may behave differently. These discounts illustrate syntax rather than specify a real sales policy.

Check decimal midpoint and negative-position rounding

SELECT ROUND(CAST('1.235' AS DECIMAL(10,3)),2),
 ROUND(CAST('-1.235' AS DECIMAL(10,3)),2);
SELECT ROUND(12345,-2);

The explicit DECIMAL inputs round to 1.24 and -1.24. Rounding 12345 to position -2 gives 12300. Do not assume that approximate floating-point inputs follow the same midpoint behavior as exact decimal inputs; inspect exponent notation and expressions involving DOUBLE.

Summing already-rounded rows can differ from rounding the final sum. Determine what an invoice or tax policy requires, then test fractional boundary values against that rule instead of choosing a result because it looks convenient.

Make conversions explicit and identify lost information

SELECT CAST('12.90' AS DECIMAL(10,2)),
 CAST('0012' AS UNSIGNED), CAST(123 AS CHAR);

The results are 12.90, 12 and the string 123. DECIMAL(10,2) provides ten total digits, including two fractional digits. Converting an identifier to a number loses its leading zeros; product codes and postal codes may belong in string columns because they are not quantities.

An explicit CAST does not establish that an input satisfies business rules. Consider fractional reduction, out-of-range values and negative-to-unsigned conversions as distinct cases. Check the supported target types rather than treat CAST as generic input validation.

Inspect malformed numeric text and warnings

SELECT CAST('12abc' AS DECIMAL(10,2));
SHOW WARNINGS;

This SELECT can convert the numeric prefix of the malformed string to 12.00 while issuing a truncation warning. A numeric result does not prove that the entire original string was valid. Run SHOW WARNINGS immediately and also validate accepted formats and ranges at the input boundary.

The same bad value may cause a warning or an error depending on whether it is selected or stored, the SQL mode and the destination type. Correct the input or schema instead of disabling strict mode simply to suppress an error. Preserving source data helps trace a conversion problem.

Compare each calculation stage and clean up

Read the primary key, original price, intermediate calculation and final rounded value together. Investigate NULL inputs before substituting zero, and keep currency symbols and grouping commas out of numeric values. Before changing stored prices with UPDATE, verify the target rows and calculations through SELECT.

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