Re: ORDER BY calculated field

[Sebastian Mendel]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: ORDER BY calculated field

[Neil Tompkins]

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



 Date: Thu, 20 Mar 2008 11:36:30 +0100 From: [EMAIL PROTECTED] 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 JOIN 
 Products 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/[EMAIL PROTECTED] 
_
Telly addicts unite!
http://www.searchgamesbox.com/tvtown.shtml

Re: ORDER BY calculated field

[Sebastian Mendel]

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)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY calculated field

[Sebastian Mendel]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY calculated field

[Sebastian Mendel]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: ORDER BY calculated field

[Neil Tompkins]

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: [EMAIL PROTECTED] To: 
 mysql@lists.mysql.com Subject: Re: ORDER BY calculated field  Sebastian 
 Mendel schrieb:  Neil Tompkins schrieb:  HiI want to order by 
 the totalled fields varProductCount and   Products.ProductReviewDESC   
  just put them together, separated with comma, like it is written in the   
 manualORDER 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/[EMAIL 
 PROTECTED] 
_
Share what Santa brought you
https://www.mycooluncool.com

RE: ORDER BY calculated field

[Neil Tompkins]

Thanks for the help.  However I found another solution which is to use the 
following SELECT COUNT(ProductsPurchases.ProductID)+Products.ProductReview 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: [EMAIL PROTECTED] 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  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  --  
 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql 
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 
_
Share what Santa brought you
https://www.mycooluncool.com