jeudi 22 septembre 2016

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

Aucun commentaire:

Enregistrer un commentaire