SQL Production Analytics: Planned vs Actual Production

Learn how to use SQL to compare planned production with actual output in a manufacturing environment.

In a Smart Factory, production data is not valuable simply because there are thousands of records. Its value comes from the questions we can answer with it.

One of the first questions a production manager asks is simple:

Are we producing what we planned to produce?

This article uses the Smart Factory Manufacturing Dataset to answer that question with PostgreSQL and SQL.

1. Planned production vs actual production

Our manufacturing database contains production orders with quantities that were planned and quantities that were actually produced.

At a high level, we can think about the data like this:

• planned_quantity → what the factory expected to produce
• actual_quantity → what was actually produced
• rejected_quantity → production that did not meet quality requirements

The difference between planned and actual production is one of the simplest and most useful manufacturing KPIs.

2. Your first production query

Let's start by looking at production orders directly.

SELECT
    order_id,
    product_id,
    planned_quantity,
    actual_quantity,
    rejected_quantity
FROM production_orders
ORDER BY order_id
LIMIT 20;

This query does not calculate anything yet. It simply lets us inspect the production data.

When learning Industrial SQL, this step is important: understand the data before calculating KPIs.

3. How much did the factory actually produce?

We can aggregate production across all production orders.

SELECT
    SUM(planned_quantity) AS planned_production,
    SUM(actual_quantity) AS actual_production
FROM production_orders;

Now we have two numbers that can immediately be compared.

If actual production is lower than planned production, the factory has a production gap.

4. Calculate the production gap

Instead of returning two separate values, we can calculate the difference directly in SQL.

SELECT
    SUM(planned_quantity) AS planned_production,
    SUM(actual_quantity) AS actual_production,
    SUM(actual_quantity) - SUM(planned_quantity) AS production_gap
FROM production_orders;

A negative production gap means that the factory produced less than planned.

A positive value means that actual production exceeded the planned quantity.

5. Calculate the fulfillment rate

A more useful KPI is the production fulfillment rate.

It answers:

What percentage of planned production was actually achieved?

The formula is:

Fulfillment Rate = Actual Production / Planned Production × 100

We can calculate it directly in PostgreSQL:

SELECT
    SUM(planned_quantity) AS planned_production,
    SUM(actual_quantity) AS actual_production,
    ROUND(
        SUM(actual_quantity)::numeric
        / NULLIF(SUM(planned_quantity), 0) * 100,
        2
    ) AS fulfillment_rate
FROM production_orders;

The NULLIF() protects the calculation from division by zero, while the cast to numeric allows PostgreSQL to calculate a precise percentage.

6. Which production line performs best?

A factory rarely has only one production line.

Our dataset contains multiple production lines, so we can compare their performance.

SELECT
    pl.line_code,
    pl.line_name,
    SUM(po.planned_quantity) AS planned_quantity,
    SUM(po.actual_quantity) AS actual_quantity,
    ROUND(
        SUM(po.actual_quantity)::numeric
        / NULLIF(SUM(po.planned_quantity), 0) * 100,
        2
    ) AS fulfillment_rate
FROM production_orders po
JOIN production_lines pl
    ON pl.line_id = po.line_id
GROUP BY
    pl.line_code,
    pl.line_name
ORDER BY fulfillment_rate DESC;

Now SQL becomes an industrial analytics tool rather than simply a database language.

We are joining production orders with their production lines, aggregating the quantities, calculating a KPI and ranking the lines.

7. Find the underperforming production line

We can reverse the previous ordering to find the weakest production lines.

SELECT
    pl.line_code,
    pl.line_name,
    SUM(po.planned_quantity) AS planned_quantity,
    SUM(po.actual_quantity) AS actual_quantity,
    ROUND(
        SUM(po.actual_quantity)::numeric
        / NULLIF(SUM(po.planned_quantity), 0) * 100,
        2
    ) AS fulfillment_rate
FROM production_orders po
JOIN production_lines pl
    ON pl.line_id = po.line_id
GROUP BY
    pl.line_code,
    pl.line_name
ORDER BY fulfillment_rate ASC;

This is already a useful operational question:

Which production line requires attention?

But the answer is only the beginning of the investigation.

8. Compare production by product

The problem may not come from an entire production line. A particular product could be responsible for the production gap.

SELECT
    p.product_code,
    p.product_name,
    SUM(po.planned_quantity) AS planned_quantity,
    SUM(po.actual_quantity) AS actual_quantity,
    ROUND(
        SUM(po.actual_quantity)::numeric
        / NULLIF(SUM(po.planned_quantity), 0) * 100,
        2
    ) AS fulfillment_rate
FROM production_orders po
JOIN products p
    ON p.product_id = po.product_id
GROUP BY
    p.product_code,
    p.product_name
ORDER BY fulfillment_rate ASC;

This gives us another analytical dimension:

• Which products are meeting their targets?
• Which products are underperforming?
• Is the problem associated with a particular product family?

9. Look at production over time

Manufacturing performance should also be analyzed over time.

We can aggregate production by order date.

SELECT
    order_date,
    SUM(planned_quantity) AS planned_quantity,
    SUM(actual_quantity) AS actual_quantity,
    SUM(actual_quantity) - SUM(planned_quantity) AS production_gap
FROM production_orders
GROUP BY order_date
ORDER BY order_date;

This result can later be visualized as a production trend in a dashboarding tool such as Grafana.

10. Why this matters in a Smart Factory

A production KPI rarely exists in isolation.

If a production line is underperforming, the next questions become more interesting:

• Did machines experience downtime?
• Did maintenance events occur?
• Did quality problems increase?
• Did rejected quantities increase?
• Did energy consumption change?

This is why a manufacturing database needs several interconnected data domains.

Production tells us what happened.

Downtime can help explain why production stopped.

Quality can help explain why output was rejected.

Maintenance can help explain why machine performance changed.

Energy data can reveal another dimension of operational performance.

11. Your Industrial SQL challenge

Now try to investigate the dataset yourself.

1. Find the production line with the lowest fulfillment rate.
2. Calculate its total production gap.
3. Identify the products produced on that line.
4. Compare planned and actual production by product.
5. Find the dates with the largest production gaps.
6. Investigate whether downtime events occurred during the same period.

Do not stop at the first KPI. In industrial analytics, the objective is to move from a symptom to a possible cause.

12. Practice with the complete Smart Factory dataset

The Smart Factory Manufacturing Dataset v0.1 provides a complete synthetic manufacturing environment designed for hands-on PostgreSQL and Industrial SQL practice.

• Production lines
• Industrial machines
• Products
• Production orders
• Production events
• Downtime events
• Maintenance events
• Quality inspections
• Energy measurements

Instead of practicing SQL on generic customer or sales databases, you can investigate a connected manufacturing environment and build industrial analytics queries step by step.

Ready to investigate the factory?

Get the complete Smart Factory Manufacturing Dataset v0.1 and practice PostgreSQL with a realistic synthetic industrial environment.

GET THE DATASET — $9

What's next?

In the next article, we will move from production performance to another critical manufacturing problem:

Why do machines stop?

We will use SQL to analyze downtime events, identify the machines responsible for the most lost time and begin connecting operational events to production performance.

Next: SQL Machine Downtime Analysis — Finding Where Production Stops