Adding child record count to query results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding child record count to query results

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2012-07-26 : 12:58:45

I have two tables with a master-detail relationship, the parent (policy) can have zero or more children (activities) and when I query a list of policies I need to know how many activities there are.

With the help of this forum I ended up with this query using the OVER and PARTITION clauses:

SELECT p.* ,
COUNT(a.ID) OVER(PARTITION BY p.ID) AS xActivityCount
FROM Policies p
LEFT JOIN PolicyActivity a ON p.ID = a.PolicyID
WHERE p.ClientID=@ClientID

Problem is the results set contains one record for each activity, so some policies are duplicated.

Any ideas on how to fix this?

kpg

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-26 : 13:08:04

SELECT DISTINCT p.* ,
COUNT(a.ID) OVER(PARTITION BY p.ID) AS xActivityCount
FROM Policies p
LEFT JOIN PolicyActivity a ON p.ID = a.PolicyID
WHERE p.ClientID=@ClientID

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 13:12:39

anotherway


SELECT p.*,
COALESCE(ActivityCnt,0) AS ActivityCnt
FROM Policies p
OUTER APPLY (SELECT COUNT(1) AS ActivityCnt
             FROM PolicyActivity
             WHERE PolicyID = p.ID
             )a
WHERE p.ClientID = @ClientID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2012-07-26 : 13:13:50

quote:
Originally posted by lionofdezert

SELECT DISTINCT p.* ,
COUNT(a.ID) OVER(PARTITION BY p.ID) AS xActivityCount
FROM Policies p
LEFT JOIN PolicyActivity a ON p.ID = a.PolicyID
WHERE p.ClientID=@ClientID

--------------------------
http://connectsql.blogspot.com/

One of my fileds is datatype xml and DISTINCT complains that it (xml) is not comparable.

kpg

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2012-07-26 : 13:29:46

quote:
Originally posted by visakh16

anotherway

SELECT p.*,
COALESCE(ActivityCnt,0) AS ActivityCnt
FROM Policies p
OUTER APPLY (SELECT COUNT(1) AS ActivityCnt
FROM PolicyActivity
WHERE PolicyID = p.ID
)a
WHERE p.ClientID = @ClientID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

That seems to do it - but I had to remove some of the parens to resolve a syntax error I was getting. Final query looks like this:

SELECT p.*,
COALESCE(xActivityCount,0) AS xActivityCount
FROM Policies p
OUTER APPLY (SELECT COUNT(1) AS xActivityCount
FROM PolicyActivity WHERE PolicyID = p.ID ) a
WHERE p.ClientID = @ClientID

Well, I also have some additional joins that go in there but I tested that and it works.

Coalese? Outer Apply? - huh, now my brain hurts. More stuff to look up.

Thanks!
kpg

kpg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 14:18:41

quote:
Originally posted by kpgraci

quote:
Originally posted by visakh16

anotherway

SELECT p.*,
COALESCE(ActivityCnt,0) AS ActivityCnt
FROM Policies p
OUTER APPLY (SELECT COUNT(1) AS ActivityCnt
FROM PolicyActivity
WHERE PolicyID = p.ID
)a
WHERE p.ClientID = @ClientID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

That seems to do it - but I had to remove some of the parens to resolve a syntax error I was getting. Final query looks like this:

SELECT p.*,
COALESCE(xActivityCount,0) AS xActivityCount
FROM Policies p
OUTER APPLY (SELECT COUNT(1) AS xActivityCount
FROM PolicyActivity WHERE PolicyID = p.ID ) a
WHERE p.ClientID = @ClientID

Well, I also have some additional joins that go in there but I tested that and it works.

Coalese? Outer Apply? - huh, now my brain hurts. More stuff to look up.

Thanks!
kpg

kpg

see
http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-26 : 23:19:27

Seems like you're making it a bit over complicated. Here it is in more traditional SQL (I think :) )

SELECT p.*, a.cnt
FROM Policies p
LEFT OUTER JOIN
(
select policyID, count(*) cnt from PolicyActivity group by policyID
) a
ON p.ID = a.PolicyID
WHERE p.ClientID=@ClientID

Also, I don't think lionofdezert's solution would work XML or not.

Assuming you are doing everything right and have a PK on a table, DISTINCT cannot make any difference if you select all columns (p.*) can it?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-26 : 23:24:16

or:
SELECT p.*, (select count(*) cnt from PolicyActivity a where p.ID = a.PolicyID) as cnt
FROM Policies p
WHERE p.ClientID=@ClientID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources