Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc
a 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 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk <[EMAIL PROTECTED]> wrote: > 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/ >
