I have the below table( I need them for oracle and sql server):

```
id  id_reference
1    0
2    1
3    1
4    1

6    2
7    2
8    3
9    8
10   0
11   10
12   10
13   12

```

I want to get the count of the id\_reference for each id.

the result 

```
id   count(1)
1   7 -- because id 1 2 3 4 and the child 6 7 8 9 are referring to the id
2   2 -- because id 6 and 7 are referring to it
3   2 -- because id 8 and the child 9 referring to it
4   0 -- non are referring to them
6   0 -- non are referring to them   
7   0 -- non are referring to them   
8   1 -- because 9 is referring to the id
10  3 -- because 11 , 12 and 13 are referring
11  0 -- none are referring
12  1 -- 13 is referring to id
13  0 -- none is referring to id

```

this what I tried but I need it to be recursive.

```
select count(1),
                 id,
                 (select count(1) from tab e2 where e2.id <=e1.id and id_ref in ( select id from tab e3 where e3.id_ref= e2.id )
            from tab e1
           group by id 
           order by id desc

```