Oracle SQL Query to Perform ABC Analysis for Inventory Management

Inventory management often feels like solving a complex puzzle where you need to identify which products deserve your maximum attention and resources.

ABC analysis offers a systematic approach to categorizing inventory items based on their value contribution, helping businesses focus on what matters most.

This powerful technique divides inventory into three categories: A items (high value, low quantity), B items (moderate value and quantity), and C items (low value, high quantity).

Oracle SQL provides robust capabilities to implement ABC analysis efficiently, enabling data-driven decision-making for inventory optimization.

Understanding how to leverage SQL queries for this analysis can transform your inventory management strategy from reactive to proactive.

What Is ABC Analysis in Inventory Management?

ABC analysis represents a inventory categorization method based on the Pareto principle, which suggests that roughly 80% of effects come from 20% of causes.

In inventory context, this translates to approximately 20% of items contributing to 80% of the total inventory value.

Category A typically includes items that represent 70-80% of total value but only 10-20% of total items.

Category B encompasses items contributing 15-25% of value and representing 20-30% of inventory items.

Category C contains the remaining items that contribute 5-10% of value but constitute 50-70% of total items.

This classification helps businesses allocate resources efficiently, implement appropriate control measures, and optimize working capital.

Why Should Businesses Implement ABC Analysis?

Implementing ABC analysis delivers multiple strategic advantages for inventory management.

Resource allocation becomes more efficient when you know which items require closer monitoring and tighter controls.

High-value A items justify more frequent reviews, better forecasting methods, and stricter security measures.

Cost reduction opportunities emerge through optimized ordering patterns and storage arrangements for different categories.

Customer satisfaction improves when critical A items maintain higher availability through better stock management.

Decision-making becomes data-driven rather than intuition-based, leading to measurable improvements in inventory performance.

See also: Oracle SQL Query to Calculate Churn Rate

What Data Do You Need for ABC Analysis?

Before performing ABC analysis, you need specific inventory data points in your database.

The essential data elements include item codes or SKUs, item descriptions, unit costs, and annual consumption quantities.

Let's create a sample inventory table and populate it with data to demonstrate the analysis:

-- Create the inventory table
CREATE TABLE ex_iabc_inventory (
    item_id VARCHAR2(20) PRIMARY KEY,
    item_description VARCHAR2(100),
    unit_cost NUMBER(10,2),
    annual_quantity NUMBER(10),
    last_updated DATE
);

-- Insert sample data
INSERT INTO ex_iabc_inventory VALUES ('ITM001', 'High-End Server', 5000.00, 50, DATE '2025-01-15');
INSERT INTO ex_iabc_inventory VALUES ('ITM002', 'Desktop Computer', 800.00, 200, DATE '2025-01-10');
INSERT INTO ex_iabc_inventory VALUES ('ITM003', 'Wireless Mouse', 25.00, 1500, DATE '2025-01-12');
INSERT INTO ex_iabc_inventory VALUES ('ITM004', 'USB Cable', 5.00, 3000, DATE '2025-01-08');
INSERT INTO ex_iabc_inventory VALUES ('ITM005', 'Monitor 24-inch', 300.00, 150, DATE '2025-01-11');
INSERT INTO ex_iabc_inventory VALUES ('ITM006', 'Keyboard Mechanical', 75.00, 400, DATE '2025-01-09');
INSERT INTO ex_iabc_inventory VALUES ('ITM007', 'Laptop Premium', 1500.00, 120, DATE '2025-01-14');
INSERT INTO ex_iabc_inventory VALUES ('ITM008', 'Mouse Pad', 10.00, 2000, DATE '2025-01-07');
INSERT INTO ex_iabc_inventory VALUES ('ITM009', 'Network Switch', 2000.00, 30, DATE '2025-01-13');
INSERT INTO ex_iabc_inventory VALUES ('ITM010', 'HDMI Cable', 15.00, 1000, DATE '2025-01-06');

COMMIT;

Now let's view the data we've inserted:

SELECT * FROM ex_iabc_inventory ORDER BY item_id;

How Do You Calculate Annual Consumption Value?

The foundation of ABC analysis is calculating the annual consumption value for each inventory item.

This calculation multiplies the unit cost by the annual quantity consumed to determine the total value.

Here's how to calculate annual consumption value and rank items accordingly:

SELECT 
    item_id,
    item_description,
    unit_cost,
    annual_quantity,
    unit_cost * annual_quantity AS annual_value,
    RANK() OVER (ORDER BY unit_cost * annual_quantity DESC) AS value_rank
FROM ex_iabc_inventory
ORDER BY annual_value DESC;

What Are the Steps to Implement ABC Classification?

Implementing ABC classification requires calculating cumulative percentages and assigning categories based on predefined thresholds.

The process involves several analytical calculations that Oracle SQL handles efficiently through window functions.

First, we need to calculate the percentage contribution of each item to the total inventory value:

WITH value_calculations AS (
    SELECT 
        item_id,
        item_description,
        unit_cost,
        annual_quantity,
        unit_cost * annual_quantity AS annual_value,
        SUM(unit_cost * annual_quantity) OVER () AS total_value
    FROM ex_iabc_inventory
)
SELECT 
    item_id,
    item_description,
    annual_value,
    ROUND(annual_value / total_value * 100, 2) AS percentage_of_total
FROM value_calculations
ORDER BY annual_value DESC;

How Can You Calculate Cumulative Percentages for ABC Analysis?

Cumulative percentages are essential for determining which category each item belongs to in ABC analysis.

This calculation shows the running total of value contributions as you move through the sorted list of items.

Here's the query that calculates cumulative percentages:

WITH value_analysis AS (
    SELECT 
        item_id,
        item_description,
        unit_cost,
        annual_quantity,
        unit_cost * annual_quantity AS annual_value,
        SUM(unit_cost * annual_quantity) OVER () AS total_value,
        SUM(unit_cost * annual_quantity) OVER (ORDER BY unit_cost * annual_quantity DESC) AS cumulative_value
    FROM ex_iabc_inventory
)
SELECT 
    item_id,
    item_description,
    annual_value,
    ROUND(annual_value / total_value * 100, 2) AS percentage_of_total,
    ROUND(cumulative_value / total_value * 100, 2) AS cumulative_percentage
FROM value_analysis
ORDER BY annual_value DESC;

What Is the Complete Oracle SQL Query for ABC Classification?

Now we can combine all the calculations to create a comprehensive ABC analysis query.

This query assigns categories based on cumulative percentage thresholds: A for items up to 80%, B for 80-95%, and C for above 95%.

WITH abc_analysis AS (
    SELECT 
        item_id,
        item_description,
        unit_cost,
        annual_quantity,
        unit_cost * annual_quantity AS annual_value,
        SUM(unit_cost * annual_quantity) OVER () AS total_value,
        SUM(unit_cost * annual_quantity) OVER (ORDER BY unit_cost * annual_quantity DESC) AS cumulative_value,
        ROW_NUMBER() OVER (ORDER BY unit_cost * annual_quantity DESC) AS item_rank
    FROM ex_iabc_inventory
),
categorized_items AS (
    SELECT 
        item_id,
        item_description,
        unit_cost,
        annual_quantity,
        annual_value,
        ROUND(annual_value / total_value * 100, 2) AS percentage_of_total,
        ROUND(cumulative_value / total_value * 100, 2) AS cumulative_percentage,
        item_rank,
        CASE 
            WHEN ROUND(cumulative_value / total_value * 100, 2) <= 80 THEN 'A'
            WHEN ROUND(cumulative_value / total_value * 100, 2) <= 95 THEN 'B'
            ELSE 'C'
        END AS abc_category
    FROM abc_analysis
)
SELECT * FROM categorized_items
ORDER BY annual_value DESC;

How Can You Create Summary Statistics for Each ABC Category?

Understanding the distribution of items and values across categories helps validate the ABC analysis results.

Summary statistics provide insights into how well the classification aligns with typical ABC distribution patterns.

WITH abc_classification AS (
    -- Previous ABC analysis query
    SELECT 
        item_id,
        item_description,
        unit_cost,
        annual_quantity,
        unit_cost * annual_quantity AS annual_value,
        SUM(unit_cost * annual_quantity) OVER () AS total_value,
        SUM(unit_cost * annual_quantity) OVER (ORDER BY unit_cost * annual_quantity DESC) AS cumulative_value,
        CASE 
            WHEN SUM(unit_cost * annual_quantity) OVER (ORDER BY unit_cost * annual_quantity DESC) / 
                 SUM(unit_cost * annual_quantity) OVER () <= 0.80 THEN 'A'
            WHEN SUM(unit_cost * annual_quantity) OVER (ORDER BY unit_cost * annual_quantity DESC) / 
                 SUM(unit_cost * annual_quantity) OVER () <= 0.95 THEN 'B'
            ELSE 'C'
        END AS abc_category
    FROM ex_iabc_inventory
)
SELECT 
    abc_category,
    COUNT(*) AS item_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage_of_items,
    SUM(annual_value) AS total_category_value,
    ROUND(SUM(annual_value) * 100.0 / MAX(total_value), 2) AS percentage_of_value
FROM abc_classification
GROUP BY abc_category, total_value
ORDER BY abc_category;

What Are Practical Applications of ABC Analysis Results?

ABC analysis results enable organizations to implement differentiated inventory management strategies for each category.

Category A items require tight control with frequent reviews, accurate demand forecasting, and minimal safety stock to reduce holding costs.

These high-value items often benefit from just-in-time ordering strategies and vendor-managed inventory programs.

Category B items need moderate control with periodic reviews and balanced safety stock levels.

Regular monitoring ensures these items don't shift into Category A while maintaining reasonable service levels.

Category C items can be managed with simple controls, higher safety stocks, and bulk ordering to minimize ordering costs.

Automated reordering systems work well for these low-value, high-volume items.

How Can You Enhance ABC Analysis with Additional Metrics?

Traditional ABC analysis based solely on annual consumption value can be enhanced with additional criteria.

Multi-criteria ABC analysis considers factors like lead time, criticality, and demand variability.

Let's create an enhanced analysis table with additional metrics:

-- Create enhanced inventory table
CREATE TABLE ex_iabc_inventory_enhanced (
    item_id VARCHAR2(20) PRIMARY KEY,
    item_description VARCHAR2(100),
    unit_cost NUMBER(10,2),
    annual_quantity NUMBER(10),
    lead_time_days NUMBER(5),
    criticality_score NUMBER(3), -- 1-10 scale
    demand_variability NUMBER(5,2), -- Coefficient of variation
    last_updated DATE
);

-- Insert enhanced data
INSERT INTO ex_iabc_inventory_enhanced VALUES ('ITM001', 'High-End Server', 5000.00, 50, 45, 9, 0.15, DATE '2025-01-15');
INSERT INTO ex_iabc_inventory_enhanced VALUES ('ITM002', 'Desktop Computer', 800.00, 200, 30, 7, 0.20, DATE '2025-01-10');
INSERT INTO ex_iabc_inventory_enhanced VALUES ('ITM003', 'Wireless Mouse', 25.00, 1500, 7, 3, 0.35, DATE '2025-01-12');
INSERT INTO ex_iabc_inventory_enhanced VALUES ('ITM004', 'USB Cable', 5.00, 3000, 3, 2, 0.40, DATE '2025-01-08');
INSERT INTO ex_iabc_inventory_enhanced VALUES ('ITM005', 'Monitor 24-inch', 300.00, 150, 21, 6, 0.25, DATE '2025-01-11');

COMMIT;

Now let's view the enhanced data:

SELECT * FROM ex_iabc_inventory_enhanced ORDER BY item_id;

This enhanced analysis query incorporates multiple criteria for more nuanced categorization:

WITH multi_criteria_analysis AS (
    SELECT 
        item_id,
        item_description,
        unit_cost * annual_quantity AS annual_value,
        lead_time_days,
        criticality_score,
        demand_variability,
        -- Normalize each metric to 0-1 scale
        (unit_cost * annual_quantity) / MAX(unit_cost * annual_quantity) OVER () AS value_score,
        lead_time_days / MAX(lead_time_days) OVER () AS lead_time_score,
        criticality_score / 10.0 AS criticality_norm,
        demand_variability AS variability_score
    FROM ex_iabc_inventory_enhanced
),
weighted_scores AS (
    SELECT 
        item_id,
        item_description,
        annual_value,
        -- Calculate weighted composite score
        (0.50 * value_score + 
         0.20 * lead_time_score + 
         0.20 * criticality_norm + 
         0.10 * variability_score) AS composite_score
    FROM multi_criteria_analysis
)
SELECT 
    item_id,
    item_description,
    annual_value,
    ROUND(composite_score, 3) AS composite_score,
    CASE 
        WHEN ROW_NUMBER() OVER (ORDER BY composite_score DESC) <= 
             CEIL(COUNT(*) OVER () * 0.2) THEN 'A'
        WHEN ROW_NUMBER() OVER (ORDER BY composite_score DESC) <= 
             CEIL(COUNT(*) OVER () * 0.5) THEN 'B'
        ELSE 'C'
    END AS multi_criteria_category
FROM weighted_scores
ORDER BY composite_score DESC;

How Do You Store and Maintain ABC Analysis Results?

Creating a dedicated table to store ABC analysis results enables tracking changes over time and integration with other systems.

This approach facilitates regular updates and historical analysis of category movements.

-- Create results table
CREATE TABLE ex_iabc_analysis_results (
    analysis_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    analysis_date DATE,
    item_id VARCHAR2(20),
    annual_value NUMBER(12,2),
    percentage_of_total NUMBER(5,2),
    cumulative_percentage NUMBER(5,2),
    abc_category VARCHAR2(1),
    CONSTRAINT fk_item FOREIGN KEY (item_id) REFERENCES ex_iabc_inventory(item_id)
);

-- Create index for performance
CREATE INDEX idx_iabc_results_date ON ex_iabc_analysis_results(analysis_date);
CREATE INDEX idx_iabc_results_category ON ex_iabc_analysis_results(abc_category);

Here's a stored procedure to automate the ABC analysis process:

CREATE OR REPLACE PROCEDURE sp_run_abc_analysis
AS
    v_analysis_date DATE := to_date('01/15/2025','mm/dd/yyyy');
BEGIN
    -- Insert ABC analysis results
    INSERT INTO ex_iabc_analysis_results (
        analysis_date, item_id, annual_value, 
        percentage_of_total, cumulative_percentage, abc_category
    )
    WITH abc_calc AS (
        SELECT 
            item_id,
            unit_cost * annual_quantity AS annual_value,
            SUM(unit_cost * annual_quantity) OVER () AS total_value,
            SUM(unit_cost * annual_quantity) OVER (
                ORDER BY unit_cost * annual_quantity DESC
            ) AS cumulative_value
        FROM ex_iabc_inventory
    )
    SELECT 
        v_analysis_date,
        item_id,
        annual_value,
        ROUND(annual_value / total_value * 100, 2),
        ROUND(cumulative_value / total_value * 100, 2),
        CASE 
            WHEN cumulative_value / total_value <= 0.80 THEN 'A'
            WHEN cumulative_value / total_value <= 0.95 THEN 'B'
            ELSE 'C'
        END
    FROM abc_calc;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('ABC Analysis completed for ' || 
                         TO_CHAR(v_analysis_date, 'DD-MON-YYYY'));
END sp_run_abc_analysis;
/

Execute the procedure and view results:

-- Execute the procedure
EXEC sp_run_abc_analysis;

-- View the results
SELECT 
    analysis_date,
    item_id,
    annual_value,
    percentage_of_total,
    cumulative_percentage,
    abc_category
FROM ex_iabc_analysis_results
WHERE analysis_date = TRUNC(to_date('01/15/2025','mm/dd/yyyy'))
ORDER BY annual_value DESC;

What Performance Optimization Techniques Should You Consider?

Optimizing ABC analysis queries becomes crucial when dealing with large inventory databases containing millions of items.

Proper indexing strategies significantly improve query performance for ABC calculations.

Create these indexes to enhance performance:

-- Create composite index for value calculations
CREATE INDEX idx_iabc_value_calc ON ex_iabc_inventory(unit_cost, annual_quantity);

-- Create covering index for complete analysis
CREATE INDEX idx_iabc_covering ON ex_iabc_inventory(
    item_id, item_description, unit_cost, annual_quantity
);

-- Gather statistics for optimizer
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => USER,
        tabname => 'EX_IABC_INVENTORY',
        cascade => TRUE
    );
END;
/

For very large datasets, consider using materialized views:

CREATE MATERIALIZED VIEW mv_iabc_daily_analysis
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
WITH abc_analysis AS (
    SELECT 
        item_id,
        item_description,
        unit_cost,
        annual_quantity,
        unit_cost * annual_quantity AS annual_value,
        SUM(unit_cost * annual_quantity) OVER () AS total_value,
        SUM(unit_cost * annual_quantity) OVER (
            ORDER BY unit_cost * annual_quantity DESC
        ) AS cumulative_value
    FROM ex_iabc_inventory
)
SELECT 
    item_id,
    item_description,
    annual_value,
    ROUND(annual_value / total_value * 100, 2) AS percentage_of_total,
    ROUND(cumulative_value / total_value * 100, 2) AS cumulative_percentage,
    CASE 
        WHEN cumulative_value / total_value <= 0.80 THEN 'A'
        WHEN cumulative_value / total_value <= 0.95 THEN 'B'
        ELSE 'C'
    END AS abc_category,
    SYSDATE AS analysis_timestamp
FROM abc_analysis;

How Can You Track ABC Category Changes Over Time?

Monitoring how items move between categories helps identify trends and validate inventory strategies.

Historical tracking reveals patterns in demand and value fluctuations.

Create a query to compare category changes between two periods:

WITH current_analysis AS (
    SELECT 
        item_id,
        abc_category
    FROM ex_iabc_analysis_results
    WHERE analysis_date = DATE '2025-01-15'
),
previous_analysis AS (
    SELECT 
        item_id,
        abc_category
    FROM ex_iabc_analysis_results
    WHERE analysis_date = DATE '2025-01-01'
)
SELECT 
    c.item_id,
    p.abc_category AS previous_category,
    c.abc_category AS current_category,
    CASE 
        WHEN p.abc_category = c.abc_category THEN 'No Change'
        WHEN p.abc_category IS NULL THEN 'New Item'
        WHEN c.abc_category IS NULL THEN 'Discontinued'
        ELSE 'Changed from ' || p.abc_category || ' to ' || c.abc_category
    END AS category_movement
FROM current_analysis c
FULL OUTER JOIN previous_analysis p ON c.item_id = p.item_id
WHERE p.abc_category != c.abc_category 
   OR p.abc_category IS NULL 
   OR c.abc_category IS NULL
ORDER BY c.item_id;

See also: Oracle SQL Full Outer Join

Conclusion

ABC analysis using Oracle SQL provides a powerful framework for categorizing inventory items based on their value contribution to optimize inventory management strategies.

The combination of window functions, analytical capabilities, and conditional logic in Oracle SQL makes it an ideal platform for implementing comprehensive ABC analysis.

Regular execution of ABC analysis helps organizations maintain optimal inventory levels, reduce carrying costs, and improve cash flow management.

The flexibility to incorporate multiple criteria beyond just annual consumption value enables more sophisticated inventory optimization strategies.

Storing historical results and tracking category movements over time provides valuable insights for continuous improvement in inventory management practices.

By leveraging Oracle SQL's robust features for ABC analysis, businesses can make data-driven decisions that balance inventory investment with service level requirements.