SQL Query to create a column to determine count from historical data-CodePudding

Given: columns "basket" & "Fruit". Output: Column "Count present in all the previous basket"

How to check if a fruit in a basket is present in all the preceding baskets and get the total count present?

For ex: Basket 2 contains Berry, Banana and Orange, now i need to check basket 1 to determine the count of these fruits. In the same way, for the fruits in basket 3, basket 1 and basket 2 are checked.

How can i do this using an SQL query? Currently i'm doing this on the application side using loops, rowfilter etc which consumes a lot of times as i've more than million rows.

CodePudding user response：

It appears you need a simple correlated subquery, such as:

select *, (
  select Count(*) from t t2 
  where t2.basket < t.basket 
    and t2.fruit = t.fruit
) "Count in prev baskets"
from t;

CodePudding user response：

You can also go with a window function I think. I am subtracting 1 to avoid the first count for each fruit. Maybe someone can provide a more elegant solution.

select *, 
       (count(*) over (partition by fruit order by basket) - 1) 
from t 
order by basket, fruit;