Liviu Anca Liviu Anca - 2 months ago 5
SQL Question

Count how many rows are for a value in SQL?

I have a table that looks like this:

[ContractId] [ContractDate] [SnapshotTimeId] [DaysPastDue] [Exposure]

Int(not unique) Datetime Int(format20160431) Int Int


The table is sorted by ContractId, ContractDate.

Now, I would like to add a 6th column, let's call it Unique, which has value 1 for the first ContractId value then adds 1 until it bumps across the next ContractId. Basically, I want to know how many rows I have for each ContractId and put the values, incrementally, in a column.

Edit: I want the output to look like this

>DocumentId ContractDate SnapshottimeId DPD Exposure Unique
>1 31-Aug-15 31-Aug-15 0 500 1
>1 31-Aug-15 30-Sep-15 5 450 2
>1 31-Aug-15 31-Oct-15 35 450 3
>1 31-Aug-15 30-Nov-15 7 350 4
>1 31-Aug-15 31-Dec-15 37 350 5
>1 31-Aug-15 31-Jan-16 67 340 6
>2 31-Aug-15 30-Jun-14 3 800 1
>2 31-Aug-15 31-Jul-14 15 760 2
>2 31-Aug-15 31-Aug-14 45 750 3
>2 31-Aug-15 30-Sep-14 75 750 4
>2 31-Aug-15 31-Oct-14 0 630 5
>2 31-Aug-15 30-Nov-14 15 590 6
>2 31-Aug-15 31-Dec-14 45 580 7

Answer

I think you want row_number():

select t.*,
       row_number() over (partition by contractid order by contractdate) as seqnum
from t;

This will put an incremental value, which is what I think you are describing.

If you just want the count of rows for each contract in each row, then use:

select t.*,
       count(*) over (partition by contractid) as cnt
from t;

This would put "6" in each row, if there are six rows for a the contract.