Lets say i have the following sql table:
x
id item_count texture_count
30 82 205
30 133 406
35 253 658
30 552 1004
35 234 475
35 119 288
30 104 115
36 594 1422
How can i modify my select query to return my data with an additional column telling me the number of occurrences for each ID, while keeping the original columns collected? Which would result in this:
id item_count texture_count id_count
30 82 205 4
30 133 406 4
35 253 658 3
30 552 1004 4
35 234 475 3
35 119 288 3
30 104 115 4
36 594 1422 1
Query is this:
SELECT
CAST ((2 * FLOOR(idvalue / 2)) AS INT) as id, -- im creating an id based on this operation, currently id value is a float
item_count,
texture_count
FROM
db
Advertisement
Answer
You can use window functions, if your database supports them. What you ask for is as simple as a window count
:
select
t.*,
count(*) over(partition by id) id_count
from mytable