CODEWITHSUNDEEP
mysql
smz
smz

Reputation: 173

How to count the total items under the parent sub category in mysql

I have two tables below

subcategorytbl

 sub_id    sub_title     sub_parent_id
   1        For rent           0
   2        Car                1
   3        Motorcycle         1 
   4        For Sale           0 
   5        Boat               4

itemtbl

  item_id    sub_id
    1          2
    2          2  
    3          3
    4          1
    5          2
    6          5

Car and Motorcycle are under For rent and Boat is under For Sale subcategory. Therefore,the result should be like this:

For Rent(5) 
- Car(3)
- Motorcycle(1)
For Sale(1) 
- Boat(1)

Below is my query:

  SELECT  count(*) as itemcount ,  sub_parent_id  from 
   subcategorytbl
  LEFT JOIN  itemtbl ON   subcategorytbl.sub_id=itemtbl.sub_id  
  GROUP BY   subcategorytbl.sub_id

Upvotes: 4

Views: 232

Answers (2)

Avinash Mendse
Avinash Mendse

Reputation: 57

select concat(if(a.sub_parent_id>0," - ",""), a.sub_title,'(',count(itb.it_id),')') from subcategorytbl a inner join (select sub_id as sid,sub_id as chid from subcategorytbl where sub_parent_id=0 union
select sub_parent_id as sid,sub_id as chid from subcategorytbl where sub_parent_id>0
union select sub_id as sid,sub_id as chid from subcategorytbl where sub_parent_id>0
 ) b on b.sid=a.sub_id  
 inner join (select item_id as it_id,sub_id  as itsid from itemtbl) itb on itb.itsid=b.chid 
 group by a.sub_title order by a.sub_id;

Output 

For rent(5)
 - Car(3)
 - Motorcycle (1)
For Sale(1)
 - Boat(1)

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26871

What you need here is the GROUP BY ... WITH ROLLUP option:

 SELECT  count(*) as itemcount ,  sub_parent_id  from 
   subcategorytbl
  LEFT JOIN  itemtbl ON   subcategorytbl.sub_id=itemtbl.sub_id  
  GROUP BY   subcategorytbl.sub_id WITH ROLLUP

Upvotes: 2

Related Questions