Solved

Query Help: Count Subcategories in Category

Posted on 2011-03-06
2
398 Views
Last Modified: 2012-06-21
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)
0
Comment
Question by:theideabulb
2 Comments
 
LVL 19

Expert Comment

by:Jones911
ID: 35048052
select count(cat_name) as catCount, catname
from tableName
group by cat_Name
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 35048281
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

778 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