Query Help: Count Subcategories in Category

I have a table that is setup with 3 columns

cat_id
cat_name
cat_parent_id

I want to just a list of the categories then show the count of subcats if they exist

So it may look like this

Category 1
Category 2(4)
Category 3(12)
Category 4(1)
Category 5
Category 6(3)
theideabulbAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
If you want to include records with 0 subcategories, you'll need an OUTER JOIN.  

SELECT p.cat_name, COUNT(c.cat_id) AS SubCatCount
FROM   YourTable p 
            LEFT JOIN YourTable c ON p.cat_id = c.cat_parent_id
GROUP BY p.cat_name

Open in new window


To display top level categories only, add a filter. For example if top categories have a "cat_parent_id" of zero, then

SELECT p.cat_name, COUNT(c.cat_id) AS SubCatCount
FROM   YourTable p 
            LEFT JOIN YourTable c ON p.cat_id = c.cat_parent_id
WHERE p.cat_parent_id = 0 
GROUP BY p.cat_name

Open in new window

0
 
Jones911Commented:
select count(cat_name) as catCount, catname
from tableName
group by cat_Name
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.