CODEWITHSUNDEEP
sql
SQLLighter
SQLLighter

Reputation: 11

Get count of Number of Children a row has

I have a table containing a list of products, some products have a parent stored in the table and I want to show the number of children that a product has.

ProductID ParentID
1 ------ ------NULL
2------- ------NULL
3 ------ -----------1
4 ------- ----------1

Would Then Show As:

ProductID ParentID CountOfChild
1 ------ ------NULL --------2
2------- -----NULL ---NULL
3 ------ ----------1 ---NULL
4 ------- ----------1 ----NULL

I Tried the Statement

SELECT ProductID, ParentID, COUNT(ParentID)
FROM Product
GROUP BY ProundID

But no success I am really not sure how to go about this.

Thanks.

Upvotes: 1

Views: 55

Answers (2)

madbird
madbird

Reputation: 1379

This will show you a products that have any children:

SELECT ParentID, COUNT(DISTINCT ProductID) FROM Product GROUP BY ParentID

If you need all the products (even with no children), just add the products with no children:

SELECT ProductID, 0 FROM Product WHERE ProductID NOT IN (SELECT DISTINCT ParentID FROM Product)
UNION ALL
SELECT ParentID, COUNT(DISTINCT ProductID) FROM Product GROUP BY ParentID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One method is a correlated subquery. Note that this returns 0 rather than NULL which seems sensible to me:

select p.*,
       (select count(*)
        from product p2
        where p2.parentId = p.productId
       ) as num_children
from product p;

Upvotes: 1

Related Questions