SELECT c.name category,AVG(DATEDIFF(p.expiry_period,CURRENT_DATE)) avg_shelf_life,COUNT(p.id) product_count FROM categories c JOIN products p ON c.id=p.category_id WHERE p.expiry_period IS NOT NULL GROUP BY c.id,c.name ORDER BY avg_shelf_life;