CODEWITHSUNDEEP
sqlgoogle-bigquery
user11035754
user11035754

Reputation: 227

Count the same occurrences from one column and store the incremental count in a new column

For a given pair of cat_id, subcat_id, I want to count the occurrences of the same brand_id and store the incremental count in a new column called counter:

cat_id  subcat_id   product_code customer_id  quantity  brand_id    
----------------------------------------------------------------
  123      456        AB,CD         111          2         1         
  123      456          CD          111          3         1          
  123      456          AB          222          2         1          
  123      789        AB,CD         111          2         2          
  123      789          CD          111          3         2          
  123      789          AB          222          2         2

The result should be:

cat_id  subcat_id   product_code customer_id  quantity  brand_id   counter 
---------------------------------------------------------------------------
  123      456        AB,CD         111          2         1          1
  123      456          CD          111          3         1          2
  123      456          AB          222          2         1          3
  123      789        AB,CD         111          2         2          1
  123      789          CD          111          3         2          2
  123      789          AB          222          2         2          3

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

It looks like you want row_number():

select t.*,
       row_number() over (partition by cat_id, subcat_id order by customer_id, quantity)
from t;

Upvotes: 1

Related Questions