Nicolas Jamal Nicolas Jamal - 6 months ago 20
SQL Question

SQL Select Query

I have the following SQL table:

CarId | CustomerId | Price
1 | 1 | 5000
2 | 3 | 6000
3 | 4 | 6000
4 | 1 | 6000
5 | 3 | 6000
6 | 6 | 6000


I want to create a new column:

CarId | CustomerId | Price | CarPerCustomer
1 | 1 | 5000 | 1/2
2 | 3 | 6000 | 1/3
3 | 4 | 6000 | 1/1
4 | 1 | 6000 | 2/2
5 | 3 | 6000 | 2/3
6 | 3 | 6000 | 3/3


So basically CarPerCustomer = number of row/(Total number of cars owned by this customer). Anyone?

xdd xdd
Answer

use window functions

select CarId, CustomerId, Price
    , cast(row_number() over (partition by CustomerId order by CustomerId) as varchar)
        + '/' + cast(count(*) over (partition by CustomerId ) as varchar)
    from t