sql query

• znkin

  Old Hand

  Points: 382

  More actions

  July 19, 2004 at 6:03 am

  #161892

  I have a database table CustomerGroup which has a column GroupId which is a primary key columnn

  In another database table Customerdtl I have columns GroupId(foreign key in this table) and

  CustRpt(primary key in this table)

  (that is each different CustRpt can be associated with a same GroupId)

  How can I make a select query which will return me 2 columns where in the first column I want all the unique values

  of GroupId and in the second column I want the count of the CustRpt for that GroupId

  How do I go about doing this?

  Thanks

   

• Shaji Venugopal

  Old Hand

  Points: 348

  More actions

  July 19, 2004 at 6:33 pm

  #515139

  I think you are looking for this gropby query.

  select GroupId, count(*) as [Count] from Customerdtl

  group by GroupId

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  July 20, 2004 at 1:26 am

  #515167

  In case there are Groups with no members (no corresponding entry in Customerdtl) and you want to display these as well :

  SELECT g.GroupId, count(c.CustRpt)

  FROM CustomerGroup g

  LEFT JOIN Customerdtl c ON c.GroupId = g.GroupId

  GROUP BY g.GroupId

  HTH, Vladan