CODEWITHSUNDEEP
mysql
fred
fred

Reputation: 299

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?

Upvotes: 7

Views: 131

Answers (1)

necromancer
necromancer

Reputation: 24651

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

Upvotes: 6

Related Questions