Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

Count distinct parent and child records

I have a query which returns a individual records where a parent-child realtionship exists. I would like to count the distinct parent records and distinct child records. Each parent can have >1 child, but for some reason the number of parent records  = child records which is incorrect. Here is an example

Parent Record 001 + Child Record 001
Parent Record 001 + Child Record 002
Parent Record 001 + Child Record 003

The result I am looking for would be 1 Parent + 3 Child on a single row returned in my query. It should be fairly simple, but I am stumped.
Thanks in advance for your help.
Chad
0
cpyatt05
Asked:
cpyatt05
  • 2
1 Solution
 
peter57rCommented:
I'm not sure the question you have asked is really what you want and it involves a few steps but...


To get the distinct parents you would do...

Select distinct parentid from table (save as query1)

For children you do the same thing.

Select distinct childid from table (save as query2)

Then you do a third query:
Select distinct (Select count(*) from query1) as ParentCount, (Select count(*) from query2) as ChildCount from anysmalltable

this gives you counts of distinct parents and distinct children in the table.
0
 
cpyatt05Author Commented:
I was hoping to do all of this in one query, but your solution works fine. Thanks for your help!
0
 
cpyatt05Author Commented:
I thought it might be simple. I guess I just couldn't see it. Thanks again.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now