Liviu Anca - 1 year ago 62
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
``````

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download