php - Mysql query to select all the items in category table and total items in product table belonging to each category ordering by total count -

i have 2 tables category , products. category table has id, name , product table has id, cat_id, name, details, etc. need mysql query select each category , total count of products belonging each category , order result total count. can me?

category table

+----+------+ | id | name | +----+------+ |  1 | cat1 | |  2 | cat2 | |  3 | cat3 | +----+------+ 

product table

+----+-------+--------+    | id | name  | cat_id |     +----+-------+--------+ |  1 | prod1 |      1 |     |  2 | prod2 |      1 |     |  3 | prod3 |      3 |     |  4 | prod4 |      2 |     |  5 | prod5 |      2 | |  6 | prod6 |      2 |     +----+-------+--------+ 

expected output table

+----+------+-------+     | id | name | count |     +----+------+-------+     |  2 | cat2 |     3 |     |  1 | cat1 |     2 |     |  3 | cat3 |     1 |   +----+------+-------+ 

i don't know if group gives error. try it

    select b.cnt,a.name category left join      (select count(*) cnt,c.name,c.id  product p      left join category c.id = p.cat_id group p.cat_id)       b b.id = a.id order cnt