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

• Mike John

  SSCertifiable

  Points: 7216

  More actions

  November 25, 2010 at 1:10 am

  #1255465

  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

  November 25, 2010 at 5:45 am

  #1255546

  it really works out.

  Thanks you so much Mr.Mike.

  Regards,

  Arun

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 25, 2010 at 6:37 am

  #1255556

  -- 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

  November 25, 2010 at 7:09 am

  #1255564

  Thanks a lot Chris. Will make use of it.

  Regards,

  Arun