SEO

#1 (**permalink**) 03-20-2008, 12:47 PM

Hi,

How do I achieve a SQL statement to order my results based on two calculated fields for example :

SELECT COUNT(ProductsPurchases.ProductID) as varProductCount, Products.Name, Products.ProductReview
FROM ProductsPurchasesINNER JOIN Products ON Products.ProductID = ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY varProductCount+Products.ProductReviewDESC

Thanks
Neil
__________________________________________________ _______________
Telly addicts unite!
http://www.searchgamesbox.com/tvtown.shtml

#2 (**permalink**) 03-20-2008, 12:47 PM

Neil Tompkins schrieb:
> Hi,
>
> How do I achieve a SQL statement to order my results based on two calculated fields for example :

what two calculated fields?

> SELECT COUNT(ProductsPurchases.ProductID) as varProductCount, Products.Name, Products.ProductReview
> FROM ProductsPurchasesINNER JOIN Products ON Products.ProductID = ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY varProductCount+Products.ProductReviewDESC

ORDER BY COUNT(ProductsPurchases.ProductID)

--
Sebastian Mendel

#3 (**permalink**) 03-20-2008, 12:47 PM

Hi

I want to order by the totalled fields varProductCount and Products.ProductReviewDESC

Neil

> Date: Thu, 20 Mar 2008 11:36:30 +0100> From: lists@sebastianmendel.de> To: mysql@lists.mysql.com> Subject: Re: ORDER BY calculated field> > Neil Tompkins schrieb:> > Hi,> > > > How do I achieve a SQL statement to order my results based on two calculated fields for example :> > what two calculated fields?> > > > SELECT COUNT(ProductsPurchases.ProductID) as varProductCount, Products.Name, Products.ProductReview> > FROM ProductsPurchasesINNER JOINProducts ON Products.ProductID = ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY varProductCount+Products.ProductReviewDESC> > ORDER BY COUNT(ProductsPurchases.ProductID)> > -- > Sebastian Mendel> > -- > MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> To unsubscribe: http://lists.mysql.com/mysql?unsub=neildtompkins@hotmail.com>
__________________________________________________ _______________
Telly addicts unite!
http://www.searchgamesbox.com/tvtown.shtml

#4 (**permalink**) 03-20-2008, 12:47 PM

Neil Tompkins schrieb:
> Hi
>
> I want to order by the totalled fields varProductCount and Products.ProductReviewDESC

just put them together, separated with comma, like it is written in the manual

ORDER BY varProductCount + Products.ProductReviewDESC,
COUNT(ProductsPurchases.ProductID)

#5 (**permalink**) 03-20-2008, 12:47 PM

Sebastian Mendel schrieb:
> Neil Tompkins schrieb:
>> Hi
>>
>> I want to order by the totalled fields varProductCount and
>> Products.ProductReviewDESC
>
> just put them together, separated with comma, like it is written in the
> manual
>
> ORDER BY varProductCount + Products.ProductReviewDESC,
> COUNT(ProductsPurchases.ProductID)
>

sorry:

ORDER BY COUNT(ProductsPurchases.ProductID) + Products.ProductReviewDESC

--
Sebastian

#6 (**permalink**) 03-20-2008, 12:47 PM

Neil Tompkins schrieb:
> Thanks Sebastian, but I now get the error message
>
> [MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function

i am not familiar with ODBC or MySQL 3.x

but possible just GROUP BY is missing
check the manual for your mysql version for the exact syntax

if this is not working at all you have to use HAVING

--
Sebastian

#7 (**permalink**) 03-20-2008, 12:47 PM

Thanks Sebastian, but I now get the error message

[MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function

> Date: Thu, 20 Mar 2008 12:59:22 +0100> From: lists@sebastianmendel.de> To: mysql@lists.mysql.com> Subject: Re: ORDER BY calculated field> > Sebastian Mendel schrieb:> > Neil Tompkins schrieb:> >> Hi> >> > >> I want to orderby the totalled fields varProductCount and > >> Products.ProductReviewDESC> > > > just put them together, separated with comma, like it is written inthe > > manual> > > > ORDER BY varProductCount + Products.ProductReviewDESC,> > COUNT(ProductsPurchases.ProductID)> > > > sorry:> > ORDER BY COUNT(ProductsPurchases.ProductID) + Products.ProductReviewDESC> > -- > Sebastian> > -- > MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> To unsubscribe: http://lists.mysql.com/mysql?unsub=neildtompkins@hotmail.com>
__________________________________________________ _______________
Share what Santa brought you
https://www.mycooluncool.com

#8 (**permalink**) 03-20-2008, 12:47 PM

Thanks for the help. However I found another solution which is to use the following SELECT COUNT(ProductsPurchases.ProductID)+Products.Produc tReview as varProductCount, Products.Name, Products.ProductReviewFROM ProductsPurchasesINNER JOIN Products ON Products.ProductID = ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY varProductCount DESC

> Date: Thu, 20 Mar 2008 13:08:51 +0100> From: lists@sebastianmendel.de> CC: mysql@lists.mysql.com> Subject: Re: ORDER BY calculated field> > Neil Tompkins schrieb:> > Thanks Sebastian, but I now get the error message> > > > [MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function> > iam not familiar with ODBC or MySQL 3.x> > but possible just GROUP BY is missing> check the manual for your mysql version for the exact syntax> > if this is not working at all you have to use HAVING> > -- > Sebastian> > -- > MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> To unsubscribe: http://lists.mysql.com/mysql?unsub=neildtompkins@hotmail.com>
__________________________________________________ _______________
Share what Santa brought you
https://www.mycooluncool.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode