Hi Basically from the query below, it would only return one product like RedLight. But I need to return a list of all products, ordered by a
SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC On Fri, Oct 24, 2008 at 2:53 PM, Olexandr Melnyk <[EMAIL PROTECTED]> wrote: > 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/ >
