SQL Queries for an Inventory Management System

Every inventory system needs to answer the same core questions: how much stock do we have right now, what needs to be reordered, and what happened to a product over time. This guide uses a realistic products-and-movements schema to write the SQL queries that answer each of those questions, from current stock levels to full movement history reports.

The Schema

Rather than storing a single mutable quantity column on the product (which loses history and is prone to race conditions), a robust inventory system logs every stock change as an immutable row in a movements table:

CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(50) UNIQUE NOT NULL,
  product_name VARCHAR(150) NOT NULL,
  reorder_threshold INT NOT NULL DEFAULT 10,
  reorder_quantity INT NOT NULL DEFAULT 50
);

CREATE TABLE stock_movements (
  movement_id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  quantity_change INT NOT NULL,       -- positive = in, negative = out
  movement_type VARCHAR(20) NOT NULL, -- 'purchase', 'sale', 'return', 'damage', 'adjustment'
  movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reference_id VARCHAR(50),           -- e.g. order number or PO number
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Every purchase order receipt, sale, customer return, and damage write-off becomes a row with a signed quantity. The current stock level is never stored directly — it is always derived by summing the log, which keeps a complete audit trail.

Current Stock Level per Product

Summing quantity_change grouped by product gives the current on-hand quantity for every item:

SELECT
  p.product_id,
  p.sku,
  p.product_name,
  COALESCE(SUM(sm.quantity_change), 0) AS current_stock
FROM products p
LEFT JOIN stock_movements sm ON sm.product_id = p.product_id
GROUP BY p.product_id, p.sku, p.product_name
ORDER BY p.product_name;

The LEFT JOIN and COALESCE(..., 0) matter here — a brand-new product with no movements yet should show 0 in stock, not disappear from the report or show NULL.

Low-Stock Alerts

Wrap the stock level calculation in a subquery and compare it against each product's reorder threshold to flag items that need restocking:

SELECT *
FROM (
  SELECT
    p.product_id,
    p.sku,
    p.product_name,
    p.reorder_threshold,
    COALESCE(SUM(sm.quantity_change), 0) AS current_stock
  FROM products p
  LEFT JOIN stock_movements sm ON sm.product_id = p.product_id
  GROUP BY p.product_id, p.sku, p.product_name, p.reorder_threshold
) stock_summary
WHERE current_stock <= reorder_threshold
ORDER BY current_stock ASC;

This query is a good candidate to run on a schedule (a cron job or database event) and pipe the results into an email or dashboard alert for the purchasing team.

Reorder Report with Suggested Quantity

Extend the low-stock query to suggest how much to order, using each product's preconfigured reorder_quantity:

SELECT
  stock_summary.sku,
  stock_summary.product_name,
  stock_summary.current_stock,
  stock_summary.reorder_threshold,
  p.reorder_quantity AS suggested_order_qty
FROM (
  SELECT
    p.product_id,
    p.sku,
    p.product_name,
    p.reorder_threshold,
    COALESCE(SUM(sm.quantity_change), 0) AS current_stock
  FROM products p
  LEFT JOIN stock_movements sm ON sm.product_id = p.product_id
  GROUP BY p.product_id, p.sku, p.product_name, p.reorder_threshold
) stock_summary
JOIN products p ON p.product_id = stock_summary.product_id
WHERE stock_summary.current_stock <= stock_summary.reorder_threshold
ORDER BY stock_summary.current_stock ASC;

Stock Movement History for a Product

To audit exactly what happened to a specific SKU — every sale, restock, and adjustment in order — query the movements table directly and compute a running balance with a window function:

SELECT
  sm.movement_date,
  sm.movement_type,
  sm.quantity_change,
  sm.reference_id,
  SUM(sm.quantity_change) OVER (
    ORDER BY sm.movement_date, sm.movement_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance
FROM stock_movements sm
JOIN products p ON p.product_id = sm.product_id
WHERE p.sku = 'SKU-10245'
ORDER BY sm.movement_date, sm.movement_id;

The running balance lets you see stock level at any point in the past, which is invaluable for investigating discrepancies between physical counts and system records.

Monthly Movement Summary by Type

For a higher-level view, summarize movement volume by type and month — useful for spotting trends in sales velocity, damage rates, or return volume:

SELECT
  DATE_FORMAT(movement_date, '%Y-%m') AS month,
  movement_type,
  SUM(ABS(quantity_change)) AS total_units,
  COUNT(*) AS movement_count
FROM stock_movements
WHERE movement_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY DATE_FORMAT(movement_date, '%Y-%m'), movement_type
ORDER BY month DESC, movement_type;

In PostgreSQL, replace DATE_FORMAT(movement_date, '%Y-%m') with TO_CHAR(movement_date, 'YYYY-MM') and DATE_SUB(CURDATE(), INTERVAL 6 MONTH) with CURRENT_DATE - INTERVAL '6 months'.

Design Tips for Inventory Schemas

• Never store a mutable running quantity directly on the product row if you also need history — derive it from the movement log instead, or maintain both with a trigger for performance
• Index stock_movements(product_id, movement_date) so per-product history queries stay fast as the log grows
• Use a movement_type enum or lookup table to keep reporting consistent across purchase, sale, return, damage, and adjustment entries
• Store reference_id to link a movement back to its source order, purchase order, or adjustment ticket for traceability

Frequently Asked Questions