Hi, this is an SQL and Database Related Question

I'm working with PostgreSQL and have a question regarding WITH RECURSIVE Common Table Expressions

Suppose I have a table with 4 columns with 100 rows - the idea that all of the entries (except the first one) have an ANCESTOR that is a foregin key which references another Person (see below for sample data)

id	first	last	ancestor
1	bob	smith	0
2	jim	michealson	1
3	mary	adams	1
4	steve	singh	2
5	kim	redden	3

How can I make a query using WITH RECURSIVE to return each person with the number of total descendants of them? (For example using the sample data 1 has 2 direct descendants, and 2 'grandchildren' - so if just using just these 5 should be (recall though I have 100 rows)

id	first	last	ancestor	total descendents
1	bob	smith	0	4
2	jim	michealson	1	1

So far I have something along the lines of:

WITH RECURSIVE ANC_DEC (ancestor, id) AS ( SELECT ancestor, id FROM Person UNION SELECT D.id, A.ancestor FROM Person AS D, ANC_DEC AS A WHERE D.ancestor = A.id ) SELECT p.id, p.first, p.last, p.ancestor, (SELECT COUNT(*) FROM ANC_DEC WHERE (p.id = anc_dec.id)) FROM Person AS p;

This is returning far too many results for the 'lower' descendants and not enough for the 'older' descents (ie 1, 2, 3 should have the most total descendents)

Thanks I think once I get how the recursion works here it will help me understand it better.