SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC
2008/10/24, Tompkins Neil <[EMAIL PROTECTED]>: > > Following on from my email below I now need help with the following > problem. Here is a list of my sample data > > Date ProductID ProductName ProductScore > Quantity > 2008-11-10 100 Red Light > 0.05 10 > 2008-11-11 100 Red Light > 0.05 2 > 2008-11-12 100 Red Light > 0.05 0 > 2008-11-10 150 Blue Light > 0.01 5 > 2008-11-11 150 Blue Light > 0.01 5 > 2008-11-12 150 Blue Light > 0.01 5 > 2008-11-10 160 Green Light > 0.05 5 > 2008-11-11 160 Green Light > 0.06 5 > 2008-11-12 160 Green Light > 0.11 5 > > I need to list this data in the order of the the product with the highest > quantity, followed by ProductScore. Am I able to calculate a quantity > percentage, based on the number of records for say Red Light. > > Thanks, > Neil > > > > > On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil < > [EMAIL PROTECTED]> wrote: > >> Thanks for the reply, this is exactly what I wanted. >> >> Cheers Olexandr ! >> >> On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk <[EMAIL PROTECTED]>wrote: >> >>> SELECT ProductName FROM Products >>> WHERE ProductScore > 100 >>> ORDER BY CASE WHEN ProductScore = 125 >>> THEN 0 >>> ELSE 1 >>> END, ProductScore >>> >>> But this query won't use an index, so it would be a good idea to do this >>> in two queries >>> 2008/10/24 Tompkins Neil <[EMAIL PROTECTED]> >>> >>>> Hi >>>> >>>> I've the following basic query like >>>> >>>> SELECT ProductName FROM Products >>>> WHERE ProductScore > 100 >>>> ORDER BY ProductScore >>>> >>>> However, how can I order by ProductScore, but ensure the product with ID >>>> 125 >>>> is at the top ? Is this possible. >>>> >>>> Thanks >>>> Neil >>>> >>> >>> >>> >>> -- >>> Sincerely yours, >>> Olexandr Melnyk >>> http://omelnyk.net/ >>> >> >> > -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
