• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Count of ParentFolders

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?
1 Solution
Pratima PharandeCommented:
select count(*) from Folder where folderid=parentId
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:

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

Open in new window

HainKurtSr. System AnalystCommented:
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)
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

tonelm54Author Commented:
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

Then try
select parentID, count(*) from Folder group by parentID ?
Add parentid = @parentid if you meant that
HainKurtSr. System AnalystCommented:

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now