sql query to iterate over rows and make calculations

Question

I am using SQLITE. I have been trying to find an answer for the past couple of hours without success. The data I'm currently working for looks something like this (I'm simplifying):

Sales_period  Sales_Qty  Sales_Profit
0001                15       300
0002                20       500
0003                10       200

From this table I'm looking to deduct the following: A new column called Sales_Index which is: Sales_Profit/Sales_Qty Another new column called Sales_Goal which is the average of previous Sales_Index s for a given sales period. So for example the Sales_Goal for Sales_Period 0003 would be 22.5. Finally I'd like to return a boolean value which determines if the Sales_Goal was met for that given period. So my desired output would look like this:

Sales_period  Sales_Qty  Sales_Profit  Sales_Index  Sales_Goal Goal_Met
0001                15       300           20            -         - 
0002                20       500           25            20        Y
0003                10       200           20            22.5      N

Is there a way to do this? Any help is appreciated.

Edit: I can create a new table including the Sales_Index column. I'm having trouble with the steps after that.

Gordon Linoff · Accepted Answer · 2019-03-14 11:58:23Z

1

You can use a correlated subquery for the calculation:

with t as (
      select t.*, (sales_profit / sales_qty) as sales_index 
      from mytable t
     )
select t.*,
       (select avg(t2.sales_index)
        from t t2
        where t2.sales_period < t.sales_period
       ) as sales_goal
from t;

In the most recent versions of SQLite, you can use window functions:

select t.*, (sales_profit / sales_qty) as sales_index,
       avg(sales_profit / sales_qty) over (order by sales_period rows between unbounded preceding and 1 preceding) as sales_goal
from mytable t

answered Mar 14, 2019 at 11:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

memokerobi Over a year ago

thanks! and the boolean value should be a seperate query?

Gordon Linoff Over a year ago

@memokerobi . . . Just use a subquery and compare the the values .

Collectives™ on Stack Overflow

sql query to iterate over rows and make calculations

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related