Still doesn't make much sense to me. Could you show us how to calculate it for some of the rows above?
2008/10/24, Tompkins Neil <[EMAIL PROTECTED]>: > > Hi > > Thanks for your quick reply. The sample value for "a" would be like a > average of integer. e.g 6.01, or 10.19. > > Neil > > On Fri, Oct 24, 2008 at 2:49 PM, Olexandr Melnyk <[EMAIL PROTECTED]>wrote: > >> 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/ >> > > -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/