Get Subcategories count from Category table in T-SQL

Posted on 2010-01-07
Last Modified: 2012-05-08
Hello Dear Coders,


I have a table which i save my all categories info with Parent Category Info. Let's go on a sample.


CatID           CatName              MainCatID


1                  Electronics             0

2                  Cell Phones            1

3                  PDAs                     1

4                  Nokia                     2

5                  N73                       4

6                  Sony Ericsoon        2

7                  K700i                     6








As you guess MainCatID = 0 cats are Main Cats.


I need to list Electronics Sub Categories in SQL with a simple query. I don't want to use Cursor or Table (Not TempTable).


something like Select CatID from Categories Where MainCatID In (1,2,4,6)  -- How can find the elecktronic sub categories?


Thanks in advance.
Question by:Dhanasekaran Sengodan
    LVL 5

    Expert Comment

    Select CatId from categories(sub categroy table) a, category(maincategory table) b
    where a. MainCatID = b.MainCatID

    LVL 5

    Assisted Solution

    select  Maincatid, count(*) from categroy group by MainCatid
    LVL 14

    Accepted Solution

    i need output like this

    CatID                  count
    1                            6
    2                            4
    3                            0
    4                            1
    5                            0
    6                            1
    and so on ..............

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 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

    24 Experts available now in Live!

    Get 1:1 Help Now