SQL Select Query

Question

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?

Is it supposed to be a query result or persistent table with persistent column? — IVNSTN
– IVNSTN, Commented Jun 10, 2016 at 10:37
How to get 'Number of Row' and 'Total number of cars owned' ? — Dev Kumar
– Dev Kumar, Commented Jun 10, 2016 at 10:38
You appear to have a typo in the numbering scheme of your desired output, but I won't edit it. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 10, 2016 at 10:42

xdd · Accepted Answer · 2016-06-10 11:03:52Z

6

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

edited Jun 10, 2016 at 11:03

answered Jun 10, 2016 at 10:53

xdd

5452 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gofr1 Over a year ago

Nice one! Just add cast to nvarchar for row_number() and count

gofr1 · Accepted Answer · 2016-06-10 10:50:29Z

SELECT  y.*, 
        CAST(DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY CarId) AS nvarchar(10)) 
        + '/' + 
        CAST(cars_per_cust AS nvarchar(10)) as CarPerCustomer
FROM YourTable y
OUTER APPLY (
    SELECT COUNT(DISTINCT CarId) as cars_per_cust 
    FROM YourTable 
    WHERE CustomerId = y.CustomerId
    ) p
ORDER BY CarId, CarPerCustomer

Output:

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

Tim Biegeleisen · Accepted Answer · 2016-06-10 10:53:37Z

0

This query is a little bit tricky because you need to number the cars for each customer and this data does not exist in your table. You can generate the car numbers using a subquery. This gives the numerator of the CarPerCustomer column. For the denominator, the second part of my query computes the total number of cars per customer.

SELECT t1.CarId, t1.CustomerId, t1.Price,
    CONCAT((SELECT COUNT(*) + 1 FROM customers
            WHERE CarId < t1.CarId AND CustomerId = t1.CustomerId),
    '/', t2.numCars) AS CarPerCustomer
FROM customers t1
INNER JOIN
(
    SELECT CustomerId, COUNT(*) AS numCars
    FROM customers
    GROUP BY CustomerId
) t2
    ON t1.CustomerId = t2.CustomerId

edited Jun 10, 2016 at 10:53

answered Jun 10, 2016 at 10:40

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

SQL Select Query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related