sql query for Nested Count

Question

i have a table where i store member data with parent reference

if HU1 is parent ID i mean spos how do i count total mid under HU1 with nested child mid

for example see HU5 is under HU1 and HU6 is under HU5 then how do i count total nested MID which are under parent HU1 here total count under for HU1 is 10 how do i get this from sql query

i use mysql 5.0

i have tried

SELECT parent.mid, COUNT(child.mid) AS child_count FROM member parent INNER JOIN member child ON child.spos = parent.mid WHERE child.spos = 'HU1' GROUP BY parent.mid;

But its only show 2 instead of 10

and

SELECT mid,spos,(SELECT count(unit) from member where spos=spos and spos='HU1')as totaspos from member where spos='HU1'

its also showing only 2 instead 10

SELECT parent.mid, COUNT(child.mid) AS child_count FROM member parent INNER JOIN member child ON child.spos = parent.mid WHERE child.spos = 'HU1' GROUP BY parent.mid; But its only show 2 instead of 10 — MRRaja, Aug 23 '16 at 10:48
and " SELECT mid,spos,(SELECT count(unit) from member where spos=spos and spos='HU1')as totaspos from member where spos='HU1' " its also showing only 2 instead 10 — MRRaja, Aug 23 '16 at 10:53
See http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Serg, Aug 23 '16 at 10:54
see this http://stackoverflow.com/a/37288233/267540 for a much better way than a recursive query — e4c5, Aug 23 '16 at 11:02

score 0 · Answer 1 · answered Aug 23 '16 at 10:59

I would do that programmatically outside of the database, fetching either the entire table or new rows until no child is found.

It is possible given that there is a known maximum depth of the tree but it's a very costly operation. You'd basically have to join the table to it self the same number of times as the maximum depth.

score 0 · Answer 2 · answered Aug 23 '16 at 11:09

Here's another idea...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(spos VARCHAR(12) NOT NULL PRIMARY KEY
,lft INT NOT NULL
,rgt INT NOT NULL
);

INSERT INTO my_table VALUES
('HU1' ,1,22),
('HU2' ,2,3),
('HU3' ,4,5),
('HU4' ,6,7),
('HU5' ,8,21),
('HU6' ,9,20),
('HU7' ,10,19),
('HU8' ,11,18),
('HU9' ,12,17),
('HU10',13,16),
('HU11',14,15);

SELECT COUNT(*) 
  FROM my_table x 
  JOIN my_table y 
    ON y.lft > x.lft 
   AND y.rgt < x.rgt 
 WHERE x.spos = 'HU1';

+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set

score 0 · Accepted Answer · answered Aug 27 '16 at 22:02

Not With Sql Query But I Have Manged it With Function i want to share the full page code, hope this will help someone

<!--#include file="mrraja.asp"-->
<style>
.propertyDetails td{text-align: left; padding-left: 1em;width: 10%;}
a{cursor: pointer;}
</style>   
<div class="container">
<div class="col-md-12 wow fadeInLeft" data-wow-delay="0.4s">
<div class="living_box" style="height: 30em;overflow: auto;">
<% 
Set allspos=TheDB.Execute("SELECT COUNT(*) as totdir FROM member where spos='"&iam("mid")&"' ORDER BY sl asc")
if not allspos.eof=true then
totdirme=allspos("totdir")
else
totdirme=0
end if
%>  
<div class="living_desc">
    <a href="#" class="btn3">Full Team Detail</a>
    <p class="price">Total Directs: <%=totdirme%></p>
    </div>    
<table border="1" class="propertyDetails">
                <tbody>
                <tr>
                   <td>MID</td>
                   <td>NAME</td>
                   <td>SPONSOR</td>
       <td>MOBILE</td>
                   <td>ACTIVE</td>
                   <td>COUNT</td>
    </tr>   
<%
    Dim countrec:countrec=0
 Dim countunit:countunit=0
 Dim unt:unt=0
 Dim tunt:tunt=0   
 call GET_HASAN(CStr(iam("mid")))   
'===============  
Public Sub GET_HASAN(MRRAJA)
sel = " SELECT mid FROM member where  spos='" & MRRAJA & "' order by mid asc "
Set rs = TheDB.Execute(sel)
Do while Not rs.eof=true
If Not rs.EOF = True Then
l = rs.Fields(0)  
Else
l = 0
End If
Call GET_IDEA1((l))
rs.MoveNext    
Loop     
rs.Close
Set rs = Nothing        
End Sub
'------------------------------    
Public Sub GET_IDEA1(MRRAJA)                
call SET_ME(CStr(MRRAJA))
sel = " SELECT mid FROM member where  spos='" & MRRAJA & "' order by mid asc "
Set rs = TheDB.Execute(sel)
Do while Not rs.eof=true
If Not rs.EOF = True Then
l = rs.Fields(0)
Call GET_IDEA1((l))
Else
l = 0
levelL=levelL+1  
End If  
rs.MoveNext    
Loop    
rs.Close
Set rs = Nothing
End Sub
    '=============== 
Public Sub SET_ME(MRRAJA) 
sel = " SELECT * FROM member where  mid='" & MRRAJA & "' order by mid asc "
Set rs = TheDB.Execute(sel)
If Not rs.EOF = True Then
mid1=cstr(rs("mid"))
fname=rs("fullname")
snme=rs("spos")
sps=rs("spos")
mob=rs("mob")
address=(rs("addrs"))+" "+(rs("city"))+" "+(rs("dist"))
pkg=rs("act")
if pkg="YES" then
countunit=1
unt=unt+rs("unit")
else
countunit=0
unt=unt
end if
tunt=tunt+rs("unit") 
mob=rs("mob")
End if
rs.close
Set rs = Nothing
%>
    <tr>
                   <td><%=mid1%></td>
                   <td><%=fname%></td>
                   <td><%=snme%></td>
       <td><%=mob%></td>
                   <td><%=pkg%></td>
                   <td><%=countunit%></td>
    </tr>  
<%End Sub%>         
    <tr>
                   <td colspan="2">Total: <%=tunt%></td>
       <td colspan="2">Active: <%=unt%></td>
       <td colspan="2">Inactive: <%=tunt-unt%></td>
    </tr>              
    
                </tbody></table>    
    </div>
   </div>
      </div>
   </div></div>

sql query for Nested Count

3 Answers3