asked
how to get the counter column based on another column?
I have a table with the info about buildings, a table about expenses, a table about users and a table that does the billing. In the billing table, I need to have a counter that counts different users inside a building. I tried to do this with the ROW_NUMBER() but I can't get the desired result. This is my query so far: SELECT
r.id, r.building_id AS zID,z.name AS zName, r.user_id AS kID, k.Name AS kName, expence_id AS uID,u.name AS uName,u.price AS uPrice, IIF(u.unique=1,u.price,k.kvadratura * u.price) AS Balance, r.year,r.month, ROW_NUMBER() OVER(Partition by r.user_id ORDER BY
r.id) AS Counter FROM Bill r INNER JOIN Expences u ON r.usluga_id =
u.id INNER JOIN Building z ON r.zgrada_id =
z.id INNER JOIN User k ON r.korisnik_id =
k.id WHERE r.building_id =7 This is my output: ![alt text][1] What I would want to get is this: For every user inside a building, counter+1. ![alt text][2] [1]:
https://i.stack.imgur.com/oP5wR.png [2]:
https://i.stack.imgur.com/pu541.png
Seems like you would have to use DENSE_RANK instead of ROW_NUMBER(). In the following code I have added the counter per building as well as counter per user, use whatever suits you declare @table table ( id int, UserId int, BuildingId int ) insert @table select 24, 10, 7 union all select 25, 10, 7 union all select 26, 11, 7 union all select 27, 11, 7 union all select 24, 10, 8 union all select 25, 10, 8 union all select 26, 12, 8 union all select 27, 12, 8 select * ,dense_rank() over(partition by BuildingId order by UserId) PerBuildingCounter ,dense_rank() over(order by UserId) PerUserCounter from @table Hope it helps. Cheers.
18 People are following this question.
