MySQL: Count children and get parent rows

I want to count the total items that belong to a group of parent pages and the get the rows of the parents, for instance,

pg_id     pg_title      parent_id
1         A             1            
2         B             2
3         C             3
4         d             1
5         e             1
6         f             2
7         g             1
8         k             3
9         l             3
10        j             3

So I want to get a result like this,

ParentName    ParentID      TotalCout
A             1             3
B             2             1
C             3             3

Here is the query I came out with so far, but it does not return result correct, it doest not get the page title of the parents, but return their children page title instead,

SELECT root_pages.pg_title as ParentName,x.ParentID,x.TotalCount
FROM 
(
SELECT root_pages.parent_id as ParentID, COUNT(root_pages.parent_id) as TotalCount
FROM root_pages
WHERE root_pages.parent_id != root_pages.pg_id
AND root_pages.pg_hide != '1'
GROUP BY root_pages.parent_id
)x

LEFT JOIN root_pages
ON x.ParentID = root_pages.parent_id

GROUP BY x.ParentID

Any ideas how开发者_如何转开发 I can get the correct result that I need?

Thanks

Try this:

SELECT a.pg_title as ParentName,
             a.pg_id as ParentID,
             b.TotalCout
  FROM root_pages a INNER JOIN
         (
        SELECT parent_id, COUNT(1) as TotalCout
          FROM root_pages
           WHERE parent_id <> pg_id
        GROUP BY parent_id
         ) b 
     ON a.pg_id = b.parent_id
     AND b.TotalCout>0

select p.pg_title ParentName, p.pg_id ParentID, IFNULL(c.TotalCout,0) TotalCout
from root_pages p
left join
(
    select parent_id, count(*) TotalCout
    from root_pages
    where parent_id != pg_id
    group by parent_id
) c on c.parent_id=p.pg_id
WHERE p.pg_id = p.parent_id

Change the LEFT JOIN to INNER JOIN if you are not interested in root (parent) pages without children.

In a single select, given your data property that root := (parent_id=pg_id)

select max(case when pg_id=parent_id then pg_title end) ParentName,
       parent_id ParentID,
       count(*)-1 TotalCout
from root_pages
group by parent_id
having count(*) > 1

i think you're just joining on the wrong field. try joining x.parentid = root_pages.pg_id