SQL Select and Count Statement

Question

I have a table as follows

 --------------------------------
 ChildId | ChildName | ParentId |
 --------------------------------
    1    |     A     |    0     |
 --------------------------------
    2    |     B     |    1     |
 --------------------------------
    3    |     C     |    1     |
 --------------------------------

I would like to select data as -

 ---------------------------------------
     Id   |    Name    |     Childs    |
 ---------------------------------------
     1    |      A     |       2       |
 ---------------------------------------
     2    |      B     |       0       |
 ---------------------------------------
     3    |      C     |       0       |
 ---------------------------------------

The pseudo SQL statement should be like this-

SELECT ChildId AS Id, ChildName as Name, (Count (ParentId) Where ParentId=ChildId)

Any Help?

What if B has a child?

Giorgi Nakeuri
– Giorgi Nakeuri

2015-11-15 10:35:25 +00:00
Commented Nov 15, 2015 at 10:35 — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Nov 15, 2015 at 10:35

Giorgi Nakeuri · Accepted Answer · 2015-11-15 10:37:54Z

2

This will do the trick:

select t1.childid, t1.childname, count(*) as childs
from table t1
join table t2 on t1.childid = t2.parentid
group by t1.childid, t1.childname

answered Nov 15, 2015 at 10:37

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VenoMpie · Accepted Answer · 2015-11-15 10:40:22Z

0

Something like this?

SELECT ChildId AS Id, ChildName as Name, (SELECT COUNT(*)  FROM TestCountOver T WHERE T.ParentID = O.ChildID) FROM TestCountOver O

but that would give you all the nodes plus children who shouldn't be part of the hierarchy

If you want only nodes with children then use a cte

;WITH CTE AS (
SELECT ChildId AS Id, ChildName as Name, (SELECT COUNT(*) FROM TestCountOver T WHERE T.ParentID = O.ChildID) Cnt FROM TestCountOver O
)
SELECT *
FROM CTE
WHERE Cnt > 0

answered Nov 15, 2015 at 10:40

VenoMpie

457 bronze badges

1 Comment

VenoMpie Over a year ago

Takes me forever to write the answer :P Giorgi's answer is the better bet

jpw · Accepted Answer · 2015-11-15 10:38:32Z

0

If there is only one level you can use the approach in your pseudo code. It should look like this:

SELECT 
    ChildId AS Id, 
    ChildName as Name, 
    (select Count(ParentId) from t t_inner Where ParentId=t_outer.ChildId) children
from t t_outer
-- optionally where clause to limit result to parents that have children
where exists (select 1 from t where ParentId in (t_outer.ChildId))

but if there can be more than one level you can solve it using a recursive query if you want the total number of children for a parent (including grand-children etc).

answered Nov 15, 2015 at 10:38

jpw

45k6 gold badges71 silver badges90 bronze badges

Collectives™ on Stack Overflow

SQL Select and Count Statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related