CODEWITHSUNDEEP
sqloracle-databaselagwindow-functionslead
Gavin
Gavin

Reputation: 167

Incremental count

I have a table with a list of Customer Numbers and Order Dates and want to add a count against each Customer number, restarting from 1 each time the customer number changes, I've sorted the Table into Customer then Date order, and need to add an order count column. 

CASE WHEN 'Customer Number' on This row = 'Customer Number' on Previous Row then ( Count = Count on Previous Row + 1 ) 
Else Count = 1

What is the best way to approach this?

Customer and Dates in Customer then Date order:

Customer    Date      Count
0001        01/05/18  1 
0001        02/05/18  2
0001        03/05/18  3
0002        03/05/18  1  <- back to one here as Customer changed
0002        04/05/18  2
0003        05/05/18  1  <- back to one again

I've just tried COUNT(*) OVER (PARTITION BY Customer ) as COUNT but it doesn't seem to be starting from 1 for some reason when the Customer changes

Upvotes: 0

Views: 842

Answers (2)

Alex Poole
Alex Poole

Reputation: 191425

It sound like you just want an analytic row_number() call:

select customer_number,
  order_date,
  row_number() over (partition by customer_number order by order_date) as num
from your_table
order by customer_number,
  order_date

Using an analytic count also works, as @horse_with_no_name suggested:

  count(*) over (partition by customer_number order by order_date) as num

Quick demo showing both, with your sample data in a CTE:

with your_table (customer_number, order_date) as (
            select '0001', date '2018-05-01' from dual
  union all select '0001', date '2018-05-03' from dual
  union all select '0001', date '2018-05-02' from dual
  union all select '0002', date '2018-05-03' from dual
  union all select '0002', date '2018-05-04' from dual
  union all select '0003', date '2018-05-05' from dual
)
select customer_number,
  order_date,
  row_number() over (partition by customer_number order by order_date) as num1,
  count(*) over (partition by customer_number order by order_date) as num2
from your_table
order by customer_number,
  order_date
/

CUST ORDER_DATE       NUM1       NUM2
---- ---------- ---------- ----------
0001 2018-05-01          1          1
0001 2018-05-02          2          2
0001 2018-05-03          3          3
0002 2018-05-03          1          1
0002 2018-05-04          2          2
0003 2018-05-05          1          1

Upvotes: 0

user330315
user330315

Reputation:

It's hard to tell what you want, but "to add a count against each Customer number, restarting from 1 each time the customer number changes" sounds as if you simply want: 

count(*) over (partition by customer_number)

or maybe that should be the count "up-to" the date of the row:

count(*) over (partition by customer_number order by order_date)

Upvotes: 1

Related Questions