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?

Answers


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

Need Your Help

NHibernate - Orphans not deleting - Foreign Key set to null

nhibernate fluent-nhibernate persistence json.net

I'm sending a JSON collection from Javascript through a REST web service to be Deserialized using Json.NET and then finally updated in the DB using NHibernate (I'm using Fluent).

How to Domainkeys/DKIM email signing using the C# SMTP client?

c# email smtp dkim domainkeys

I have written an program in C# which sends out emails. Now I have a requirement to sign outbound emails using Dominkeys/DKIM, but I'm not sure how to do it.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.