Bill Anderson Bill Anderson - 6 months ago 38
SQL Question

SQL Server Sibling Records Count

I have two tables, Contacts, Accounts. I am trying to create a general function that will get the number of records associated with same parent, here is what I have:

CREATE FUNCTION [dbo].[SiblingCount](
@IDValue Integer
DECLARE @Result integer

Select @Result = Count(a.AccountID)
from Account a
where a.Status <> 'Active' and
a.ContactID = (select a2.ContactID
from Account a2 where a2.Accounnts = @IDValue)

RETURN @Result


Is there a better way to do it?


Are you really talking about siblings? In your query you do not even touch the table Contacts... I think, that you have a 1:n relation between Concats and Accounts and what you really need is a count of children...

I think this could be done much easier like this:

SELECT c.ContactID,COUNT(*) AS CountAccounts
FROM Contacts AS c
INNER JOIN Accounts AS a ON c.ContactID=a.ContactID
WHERE a.Status<>'Active'
GROUP BY c.ContactID

Add a AND c.ContactID=@IDValue to the WHERE to reduce the result to the one line you need...