Count of ParentFolders

Posted on 2011-10-12
Last Modified: 2012-08-13
Im trying to create a table of folders which I would like to be able to see if there is any children folders below.

I have a table with 3 coloums:-
folderID, ParentID, Folder

The ParentID has the value of folderID of its parent.

And I query it simply like
Select * from Folder

However I want it to be able to count the number of folders that have the parentID the same as the folderID, so something like:-
Select *, (Countif(`parentID`=`folderID`)) from Folders;

I know I need to make a subquery, but Im unsure how to do this, does anyone have any advice?
Question by:tonelm54
    LVL 39

    Expert Comment

    by:Pratima Pharande
    select count(*) from Folder where folderid=parentId
    LVL 142

    Accepted Solution

    this should do:

    Select f.*
      , (select count(*) from Folder p where p.folderid = f.parentid ) parent_count 
     from Folder f

    Open in new window

    LVL 51

    Expert Comment

    I gueess you have @folderid, and you want the count of records/folders in same parent

    select count(*) from Folder where parentId = (select parentid from folder where folderid=@folderid)

    Author Comment

    Ok, so Ive put in:-
    SELECT * , (SELECT count(*) FROM Folder WHERE `ParentID` =`folderID`) As subFolders

    Open in new window

    However it is always returning 0

    Just as an example, One of my rows has
    folderID = 1842-40090-42653
    parentID = 83770-6566-264

    If I do
    SELECT* FROM`Folder` WHERE`ParentID` LIKE '83770-6566-264';

    I get 5 rows returned

    LVL 13

    Expert Comment

    Then try
    select parentID, count(*) from Folder group by parentID ?
    LVL 13

    Expert Comment

    Add parentid = @parentid if you meant that
    LVL 51

    Expert Comment


    SELECT a.* , (SELECT count(*) FROM Folder b WHERE b.ParentID =a.folderID) As subFolders
    FROM Folder a

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now