complicated mysql query issue
i want to show in my dashboard the top 5 product, on each product i want to show the total of order, views and the percentage of where that product is based on others ex:
Game 1 for Xbox (200 orders / 1000 views) 20% Game 2 for WII (180 orders / 2100 views) 18% Game 3 for PS3 (170 orders / 390 views) 17% Game 4 for PS3 (90 orders / 1400 views) 9% Game 5 for WII (20 orders / 30 views) 2%
so 200 orders for game 1 out of 1000 orders is 20% of total orders. which means, 20% of my products were game 1
here's my query:
select products.name, products.type, products.views, count(*) as orders, ???????? from products inner join orders on (products.id = orders.product_id) group by orders.product_id
how do i get the percentage?
select products.name, products.type, count(*) as orders, count(*) * 100 / total.total as pct from products inner join orders on (products.id = orders.product_id) inner join (select count(*) as total from orders) total group by orders.product_id