SELECT p.name,COUNT(DISTINCT CASE WHEN t.type="stock_adjustment" AND t.adjustment_type="abnormal" THEN t.id END) damage_incidents,SUM(CASE WHEN t.type="stock_adjustment" THEN tsl.quantity ELSE 0 END) damaged_quantity FROM products p LEFT JOIN transaction_sell_lines tsl ON p.id=tsl.product_id LEFT JOIN transactions t ON tsl.transaction_id=t.id WHERE p.enable_stock=1 GROUP BY p.id,p.name ORDER BY damaged_quantity DESC;