SQL Stock

Problem of SQL Stock 

In this blog, we will learn how one can calculate the weighted average of the data in SQL. The dataset we have taken as an example is the SQL stock which includes the prices, document type, quantity, and many more other fields. The table structure of the same is shown as follows:

Solution

One of the most simple solutions to achieve the target of the above problem is to use the concept of CTE in SQL. Specifies a common table expression, often known as a temporary named result set (CTE). This is a result of a straightforward query and is specified inside the execution range of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. A CREATE VIEW statement’s defining SELECT statement may also include this clause.

The code for the same is shown below as an example:

with recursive
stock_temp as (
  select 
    *, 
    row_number() over(partition by product_id order by row_num) as rn
  from 
    stock_table 
)

,cte as (
  select 
    document_type, document_date, 
    product_id, qty_out, qty_in, price, 
    row_num, stock_balance, rn, 
    price as wac
  from 
    stock_temp where document_type = 'SI'

  union all

  select 
    sub.document_type, sub.document_date,
    sub.product_id, sub.qty_out,  sub.qty_in, sub.price,
    sub.row_num, sub.stock_balance,  sub.rn,
    case when sub.qty_in = 0 then main.wac else 
    ((sub.stock_balance - sub.qty_in) * main.wac + sub.qty_in * sub.price) 
      / ((sub.stock_balance - sub.qty_in)  + sub.qty_in) end as wac
  from 
    cte as main
    join stock_temp as sub 
      on (main.product_id = sub.product_id and main.rn + 1 = sub.rn)
)

select * from cte

Another easy solution for the above problem is the use of aggregate function in SQL. An aggregate function in SQL calculates several values and produces a single output. SQL has a wide range of aggregate functions, including avg, count, sum, min, max, etc. Except for the count function, an aggregate function does not take into account NULL values when doing the calculation. Example of aggregate functions is SUM, COUNT, MAX, MIN, and many more.

Also Read: java lang noclassdeffounderror