CODEWITHSUNDEEP
sql-serversql-server-2012
titatovenaar
titatovenaar

Reputation: 309

Show count on every row that counts how many rows there are based on certain conditions

I'm curious whether you know how to easily select a COUNT of how many 'children' there are within a 'parent', and show this on every row while maintaining the child as the unique key of the table.

Currently I select my table like this:

SELECT OBJECTID, PARENTID
FROM CURRENTTABLE

This is the result that I get:

CURRENTTABLE:
OBJECTID, PARENTID
OBJECT1, PARENT1
OBJECT2, PARENT1
OBJECT3, PARENT2
OBJECT4, PARENT2
OBJECT5, PARENT2

But I want it to look like this, because there are 2 objects within Parent1 and 3 objects within Parent2

CURRENTTABLE:
OBJECTID, PARENTID, COUNTOBJECTSWITHINPARENT
OBJECT1, PARENT1, 2
OBJECT2, PARENT1, 2
OBJECT3, PARENT2, 3
OBJECT4, PARENT2, 3
OBJECT5, PARENT2, 3

Any ideas how to do this easily?

Kind regards, Igor

Upvotes: 0

Views: 27

Answers (1)

Robert Sievers
Robert Sievers

Reputation: 1353

SELECT OBJECTID, 
PARENTID,
COUNT(*) OVER (PARTITION BY PARENTID) as COUNTOBJECTSWITHINPARENT
FROM CURRENTTABLE

Upvotes: 2

Related Questions