Could give us sample values for "a" field? Should it contain the same thing as in the query I've sent?
2008/10/24, Tompkins Neil <[EMAIL PROTECTED]>: > > 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/ >> > > -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
