Header table display count of detail table per row

Question

Post reply

Header table display count of detail table per row

darunsurey

Old Hand

Points: 395
More actions
November 25, 2010 at 12:58 am

#388308

Hi everyone,
I have a Table_A as a header of Table_B,
The table_A contains columns as,
GroupHeaderID
GroupName
CreatedDate
The table_B contains columns as,
GroupDetailID
GroupHeaderID [Ref. table_A]
MemberType
MemberID
I need to display the Table_A columns along with count of the table_B records per GroupHeaderid
My output would be,
GroupHeaderID GroupName MemberCount
-------------------- -------------- -------------------
1 ABC 10
Pls help me.
Thanks in advance.
Regards,
Arun

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

Sounds like a correlated subquery as one of the columns would fit the bill:

select

GroupHeaderID, GroupName, CreatedDate, (select count(*) from table_b where table_b.GroupHeaderID = table_a.GroupHeaderID ) as number_of_bs

from table_a

Or if this data is being fed to a report it may be quicker todo the count in the report tool - it all depends what youy are doing with it.

Mike

darunsurey Old Hand Points: 395 More actions · Answer 2

it really works out.

Thanks you so much Mr.Mike.

Regards,

Arun

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- use an INNER JOIN if the data permits

-- i.e. if every header has at least one line, and always will.

-- Option 2

SELECT

h.GroupHeaderID,

h.GroupName,

MemberCount = COUNT(*)

FROM table_A h

LEFT JOIN table_B l ON l.GroupHeaderID = h.GroupHeaderID

GROUP BY h.GroupHeaderID,

h.GroupName,

h.CreatedDate

-- Option 3

SELECT

h.GroupHeaderID,

h.GroupName,

MemberCount = l.LineCount

FROM table_A h

LEFT JOIN (

SELECT

GroupHeaderID,

LineCount = COUNT(*)

FROM table_B

GROUP BY GroupHeaderID

) l ON l.GroupHeaderID = h.GroupHeaderID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

darunsurey Old Hand Points: 395 More actions · Answer 4

Thanks a lot Chris. Will make use of it.

Regards,

Arun