counting sub-records

Results 1 to 6 of 6

Thread: counting sub-records

  1. #1
    the other steve Guest

    Default counting sub-records

    OK,<BR><BR>I have two heirarchical tables, parentTable, childTable, with parentid being the related key.<BR><BR>I&#039;ve wracked my brain trying to find a way to deliver the following recordset:<BR><BR>parentfield1, parentfield2, parentfield3, number_of_children WHERE number_of_children&#062;2<BR><BR>Any ideas? I think this is some kind of join with a count, but I get aggregate errors when I do this:<BR><BR>SELECT parenttable.parentid, parenttable.field1 Count(childid) AS tally FROM childtable INNER JOIN parenttable ON parenttable.parentid=childtable.parentid GROUP BY parenttable.parentid;

  2. #2
    Join Date
    Dec 1969
    Los Angeles, CA

    Default What are sub-records

    post exact SQL and the EXACT error message. Do not give us YOUR interpretation of the not type by copy&#124paste<BR><BR><BR>use group by .... having count(childid) &#062; 0<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: counting sub-records

    Just change your sql statement to:<BR><BR>SELECT parenttable.parentid, parenttable.field1, Count(childtable.childid) AS tally<BR>FROM parenttable<BR>JOIN childtable on childtable.parentid = parenttable.parentid <BR>GROUP BY parenttable.parentid, parenttable.field1<BR>HAVING COUNT(childtable.parentid) &#062; 2<BR><BR>I tested that out on one of my tables and it worked. Hope this helps

  4. #4
    the other steve Guest

    Default RE: good point

    OK,<BR><BR>First, good point, I should have posted the real code. I meant child-records when I said sub-records.<BR><BR>Second, I figured it out. The key is to include all of the parent fields (in this case from the clients table) in the GROUP BY expression.<BR><BR>This expression returns a recordset with parent records (tablename: clients) and the number of child-records (tablename: property) associated with that parent in a field called "tally".<BR><BR>I tried to use a SHAPE query, but it doesn&#039;t do this kind of stuff. You need a joined recordset.<BR><BR>SELECT clients.clientsid, clients.cbizname, Count(property.propertyid) AS tally<BR>FROM clients INNER JOIN property ON clients.clientsid = property.clientsid<BR>GROUP BY clients.clientsid, clients.cbizname;

  5. #5
    the other steve Guest

    Default That's Great

    That&#039;s the finishing touch. <BR><BR>Thanks.

  6. #6
    the other steve Guest

    Default And another add-on

    You can order by the number of children by simply tacking<BR><BR>ORDER BY COUNT(property.propertyid) on the end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts