Folks,
I am badly in need of counting every member on each level I have 5 level deep Commission model so I am able to count the first level but not able to count whole team a person have in his / her down line. I want to count how many members on each level like
on 1st level 2 members, second level 34 members, third level 7 members etc.
I am sharing my sql table here
id fname rid cnt
1 NetCash 0 3
2 Mian Salman 1 5
3 Zeeshan Murtaza 1 2
4 Usman Liaqat 1 2
5 Javed Iqbal 2 1
6 Faysal Islam 2 0
7 Waqas Pervaiz 3 0
8 Bashir Ahmad 5 0
9 Mirza Amir 2 0
10 Tauqeer Saghir 2 4
11 Asif Butt 4 0
12 Abdul Qayum 10 0
13 jhangir abbas 10 1
14 Ansar Mahmood 13 0
15 M USMAN KAYANI 10 0
16 Jabir Hussain 2 0
17 Abdul Rehman 10 0
18 Adnan javed 3 1
19 muhammad irfan 18 0
20 khurram 4 0
I need to count the whole number of members for Parent id / rid = 1
on 1st level , 2nd level … up to 5th level.
I am able to count on first level like
SELECT COUNT(*) AS counts FROM
user_tableWHERE rid = 2
if you need any clarification please comment i will explain.
Advertisement
Answer
Unless I missed something, you simply could use a GROUP BY
SELECT COUNT(id) AS counts, rid AS level FROM user_table WHERE rid<6 AND rid>0 GROUP BY rid