Weighted average accuracy (SQL)

On this blog i will show you how to calculate simple average and weighted average using SQL. 

First let's start by creating a table called accuracy

create table accuracy

 (Product char

,Actual_demand number

,Demand_plan number);

Let's add data to our new table.

insert into accuracy values('A', 100,500);

insert into accuracy values('B', 200, 400);

insert into accuracy values('C', 300, 300);

insert into accuracy values('D', 400, 200);

insert into accuracy values('E', 500, 100);

Now let's verify that we have all information needed.

select * from accuracy;

PRODUCT ACTUAL_DEMAND          DEMAND_PLAN           
------- ---------------------- ----------------------
A                                100                    500                   
B                                 200                    400                   
C                                300                    300                   
D                                400                    200                   
E                                500                    100   

Now let's start calculating simple accuracy and weighted accuracy using SQL. We will first try to calculate simple accuracy. Since our main purpose is to calculate overall accuracy at product family level, we will write SQL to aggregate all actual demand and demand plan at product family level. Once the aggregation is complete, we will take difference between actual demand and demand plan and calculate accuracy accordingly.

select  'Family(A,B,C,D,E)' as Product, 

SUM(actual_demand), 

SUM(Demand_plan), (1-(abs(sum(ACTUAL_DEMAND)-SUM(Demand_plan))/SUM(ACTUAL_DEMAND)))*100||'%' as acc 

from accuracy;  

PRODUCT   SUM(ACTUAL_DEMAND)   SUM(DEMAND_PLAN)            ACC   

----------------- ---------------------- ---------------------- -----------------------------------------

Family(A,B,C,D,E)              1500                                     1500                       100%    

Now let's calculate weighted accuracy. For weighted average,we need to calculate difference at lower level.

select  Product, 

SUM(actual_demand),

SUM(Demand_plan), 

ABS(SUM(Actual_demand-Demand_plan)) as Diff

from accuracy

group by product;

PRODUCT SUM(ACTUAL_DEMAND)     SUM(DEMAND_PLAN)       DIFF                  

------- ---------------------- ---------------------- ----------------------

D                              400                                      200                    200                   

A                             100                                       500                    400                   

B                             200                                        400                   200                   

C                             300                                        300                   0                     

E                             500                                        100                  400  

Once we find difference at lower level, we will need to roll up everything to the higher level. For that purpose we need to add more line of code to our existing SQL. In other words we will embed another SQL to the existing SQL and make existing SQL as sub query.

SELECT 'Family(A,B,C,D,E)' PRODUCT,

SUM(ACTUAL_DEMAND), 

SUM(DEMAND_PLAN), 

(1-(SUM(DIFF)/SUM(ACTUAL_DEMAND)))*100||'%' as Acc 

from

(select  Product, SUM(actual_demand)ACTUAL_DEMAND, SUM(Demand_plan) DEMAND_PLAN, ABS(SUM(Actual_demand-Demand_plan)) as Diff from accuracy

group by product) a;

PRODUCT           SUM(ACTUAL_DEMAND)     SUM(DEMAND_PLAN)       ACC                                       

----------------- ---------------------- ---------------------- -----------------------------------------

Family(A,B,C,D,E)               1500                                  1500                        20% 

In my last blog i had mentioned that 20% is not the correct accuracy percentage. Therefore we will need to change our SQL to display 40% as correct accuracy. (Please go to my previous blog for explanation on why 40% is correct accuracy.)

 SELECT 'Family(A,B,C,D,E)' PRODUCT, 

SUM(ACTUAL_DEMAND), 

SUM(DEMAND_PLAN), 

(1-(SUM(DIFF)/SUM(ACTUAL_DEMAND)))*100||'%' as Acc 

from

(select  Product, SUM(actual_demand)ACTUAL_DEMAND, SUM(Demand_plan) DEMAND_PLAN,

case

when

ABS(SUM(Actual_demand-Demand_plan)) >SUM(ACTUAL_DEMAND)

then

sum(ACTUAL_DEMAND)

else ABS(SUM(Actual_demand-Demand_plan))

end

as Diff

from accuracy

group by product) a;

PRODUCT    SUM(ACTUAL_DEMAND)     SUM(DEMAND_PLAN)    ACC                              

----------------- ---------------------- ---------------------- ----------------------------------

Family(A,B,C,D,E)             1500                 1500                                 40%                 

--- Have a good weekend ---