SQL Numeric Data Types: INT, DECIMAL, FLOAT and More

A price column defined with the wrong type can silently round your values and break revenue reports. A quantity column that accepts decimals can let bad data like 2.75 units slip into your inventory table. Numeric data types control not just what gets stored, but how calculations behave downstream.

Integer Types: Whole Numbers Only

Integer types store whole numbers with no decimal part. They are the right choice for counts, IDs, quantities, and flags.

Common integer types across databases:

| Type | Typical range | Use case | |------|--------------|----------| | TINYINT | 0 to 255 | Flags, small codes | | SMALLINT | -32,768 to 32,767 | Small counters | | INT / INTEGER | -2.1B to 2.1B | IDs, quantities | | BIGINT | Very large range | Transaction IDs, large counts |

In the ecommerce database, stock_qty in products and quantity in order_items are both integer columns. You would never want 3.5 units of a product in stock.

-- Check stock quantities across active products
SELECT p.product_name, p.stock_qty
FROM products p
WHERE p.is_active = 1
  AND p.stock_qty < 10
ORDER BY p.stock_qty ASC;

SQLite stores all integers as INTEGER regardless of whether you write INT, SMALLINT, or BIGINT in your schema. The type affinity system handles the mapping.

DECIMAL and NUMERIC: Exact Precision

DECIMAL and NUMERIC store exact values. You define both the total number of digits and the digits after the decimal point.

The syntax is DECIMAL(precision, scale). For example, DECIMAL(10, 2) stores up to 10 digits total, with exactly 2 after the decimal point. That covers values up to 99,999,999.99.

Columns like price, cost_price, total_amount, discount_amt, tax_amt, and salary in the ecommerce schema are all candidates for DECIMAL(10, 2).

-- Calculate profit margin per product using exact decimal columns
SELECT
    p.product_name,
    p.price,
    p.cost_price,
    ROUND(p.price - p.cost_price, 2) AS gross_profit,
    ROUND((p.price - p.cost_price) / p.price * 100, 2) AS margin_pct
FROM products p
WHERE p.is_active = 1
ORDER BY margin_pct DESC;

-- Total revenue and average order value from orders
SELECT
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value
FROM orders o
WHERE o.status = 'Delivered';

FLOAT and REAL: Approximate Values

FLOAT and REAL store floating-point numbers. They trade precision for range. You can store very large or very small numbers, but the values are approximations.

| Type | Precision | Typical use | |------|-----------|-------------| | REAL / FLOAT | ~7 significant digits | Scientific data, measurements | | DOUBLE PRECISION | ~15 significant digits | Higher precision approximations |

In SQLite, REAL is the native floating-point type and uses 8-byte IEEE 754 format. FLOAT and DOUBLE map to it.

-- Example: storing an approximate weight measurement
-- This is fine for non-financial data
SELECT
    p.product_name,
    CAST(p.price AS REAL) AS price_as_float
FROM products p
LIMIT 5;

Avoid FLOAT for any column where exact values matter, such as price, salary, or tax_amt. Use it for things like sensor readings, percentages used only for display, or scientific measurements.

How SQLite Handles Numeric Types

SQLite uses a concept called type affinity rather than strict types. When you declare a column type, SQLite maps it to one of five affinities: INTEGER, REAL, NUMERIC, TEXT, and BLOB.

| Declared type | SQLite affinity | |---------------|----------------| | INT, INTEGER, BIGINT | INTEGER | | REAL, FLOAT, DOUBLE | REAL | | DECIMAL, NUMERIC | NUMERIC | | PRICE, AMOUNT (custom names) | NUMERIC |

NUMERIC affinity in SQLite stores the value as an integer if it has no fractional part, and as a real number if it does. This means DECIMAL(10,2) in SQLite does not enforce precision the way PostgreSQL or MySQL does. You get flexible storage but no automatic rounding or precision enforcement.

-- SQLite will store 19.9999 without rounding it to 20.00
-- even if the column is declared DECIMAL(10,2)
-- Always use ROUND() explicitly in your queries

SELECT
    oi.order_id,
    oi.quantity,
    oi.unit_price,
    ROUND(oi.quantity * oi.unit_price, 2) AS calculated_total,
    oi.line_total
FROM order_items oi
LIMIT 10;

Common Mistakes

-- wrong
SELECT SUM(o.total_amount) / COUNT(o.order_id) AS avg_order
FROM orders o;

-- correct
SELECT ROUND(SUM(o.total_amount) / COUNT(o.order_id), 2) AS avg_order
FROM orders o;

Division of integers in some databases returns an integer result, truncating the decimal. Wrapping with ROUND() makes your intent explicit and protects against truncation.

---

-- wrong
SELECT p.product_name, p.price
FROM products p
WHERE p.price = 29.99;

-- correct
SELECT p.product_name, p.price
FROM products p
WHERE ROUND(p.price, 2) = 29.99;

Comparing floating-point values with = can fail silently because stored values may differ slightly from what you expect. Use ROUND() when matching decimal values, or compare within a small range.

---

-- wrong
SELECT p.product_name, p.stock_qty * 1.5 AS reorder_level
FROM products p;

-- correct
SELECT p.product_name, CAST(p.stock_qty * 1.5 AS INTEGER) AS reorder_level
FROM products p;

Multiplying an integer column by a decimal literal produces a float result. If your downstream logic expects whole units, cast the result back to INTEGER explicitly.

Quick Recap

• Use INTEGER for whole numbers like IDs, quantities, and flags. Use DECIMAL for money columns where exact values matter.
• FLOAT and REAL store approximate values. They are fine for measurements but will introduce rounding errors in financial calculations.
• SQLite does not enforce DECIMAL precision at storage time, so use ROUND() explicitly in your queries when exact decimal output matters.

SELECT p.product_name, ROUND(p.price, 2) AS price, ROUND(p.cost_price, 2) AS cost_price FROM products p WHERE p.is_active = 1 AND p.price > 100 ORDER BY p.price DESC;

SELECT p.product_name, ROUND(p.price, 2) AS price, ROUND(p.cost_price, 2) AS cost_price, ROUND(p.price - p.cost_price, 2) AS gross_profit FROM products p WHERE p.is_active = 1 AND (p.price - p.cost_price) > 20 ORDER BY gross_profit DESC;

SELECT p.product_name, SUM(oi.quantity) AS total_units_sold, ROUND(SUM(oi.line_total), 2) AS total_revenue FROM products p JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.product_id, p.product_name HAVING SUM(oi.line_total) > 500 ORDER BY total_revenue DESC;