Gets number of sales per category in LemonStand (paid orders only and supports products with multiple categories)

gistfile1.sql

SELECT shop_categories.name, count(shop_order_items.id)*shop_order_items.quantity as 'total_sales'
FROM shop_order_items
	LEFT JOIN shop_orders
		ON shop_order_items.shop_order_id = shop_orders.id
	
	LEFT JOIN shop_products
		ON shop_order_items.shop_product_id = shop_products.id
		
	LEFT JOIN shop_products_categories
		ON shop_products.id = shop_products_categories.shop_product_id
		
	LEFT JOIN shop_categories
		ON shop_products_categories.shop_category_id = shop_categories.id
WHERE shop_orders.payment_processed IS NOT NULL
GROUP BY shop_products_categories.shop_category_id
ORDER BY total_sales DESC