CODEWITHSUNDEEP
mysqlsql
user2231688
user2231688

Reputation: 197

SQL Count subcategories

I am trying to figure out how to use the count function in order to count how many subcategories each of my categories have.

My original query is as follows(where name = the category name):

select 
catid,
name,
pcatid
from category

I simply want to add a column (which is not included in my DB anywhere) called 'SubCategoryCount'. This column will count for each catid how many times it is being referenced my the pcatid column, if that makes sense.

A simple example DB would be

catid   name      pcatid
1       Base      NULL
2       Computers 1
3       Phones    1
4       Laptops   2
5       Dell      4

And i would like to produce

catid   name      pcatid    SubCategoryCount
1       Base      NULL      2
2       Computers 1         1
3       Phones    1         0
4       Laptops   2         1
5       Dell      4         0

But how would i achive this?

Thanks for your help

John

Upvotes: 1

Views: 2542

Answers (1)

John Woo
John Woo

Reputation: 263703

You need to join the table itself using LEFT JOIN so it will display all records in the table even if it does not have subcategory.

SELECT  a.catid,
        a.NAME,
        a.pcatid,
        COUNT(b.pcatid) SubCategoryCount
FROM    category a
        LEFT JOIN category b 
          ON a.catid = b.pcatid
GROUP   BY a.catid, a.NAME, a.pcatid

PS: This query is just a projection of result without changing the original schema of the table. it doesn't physically add new column on table category.

Upvotes: 2

Related Questions