well, i did suggest how to do that, didn't i, or perhaps you missed that part? or maybe you've not done a COUNT query before?
well, you didn't mention that originally, and that would require an ORDER BY clause, wouldn't it
SELECT cat.name AS category
, subcat.name AS subcategory
, COUNT(*) AS products
JOIN category AS subcat
ON subcat.pk_id = product.fk_category_id
JOIN category AS cat
ON cat.pk_id = subcat.parentid
WHERE product.product_title LIKE '%p%'