question

tricman10 avatar image
tricman10 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
tsqlsqlserverrownumber
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That is what I needed, thank you!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.