my query is running fine (MySQL 5)

SELECT * , (6366*acos(cos(0.853291)*cos(cities.latitude) *
cos(cities.longitude - (0.040841))+sin(0.853291)*sin(cities.latitude)))
as km
FROM proposals
INNER JOIN users ON proposals.user_id = users.id
INNER JOIN cities ON proposals.city_id = cities.id
INNER JOIN categories ON proposals.category_id = categories.id
INNER JOIN domains ON categories.domain_id = domains.id
WHERE proposals.city_id IN
(31556,37294,31547,37302,31548,31555,31546,31557,3 7236,31540,31541,33114,37244,37227,37262)
AND

(6366*acos(cos(0.853291)*cos(cities.latitude) *
cos(cities.longitude-(0.040841))+sin(0.853291)*sin(cities.latitude))) <
25.0
AND (valid_until >= '2007-01-20 16:37:41' OR valid_until IS NULL)
ORDER BY km

but if try to run a SELECT COUNT.. I get an the fatal error... :
Mysql::Error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no
GROUP columns is illegal if there is no GROUP BY clause

SELECT COUNT(proposals.id) ,
(6366*acos(cos(0.853291)*cos(cities.latitude) * cos(cities.longitude -
(0.040841))+sin(0.853291)*sin(cities.latitude))) as km
FROM proposals
......

why should I use a GROUP BY ? and on what ? ('proposals' ?)

thanks for your help

joss

On 2007-01-20 18:59:00 +0100, Josselin <EMAIL REMOVED> said:

> my query is running fine (MySQL 5)
>
> SELECT * , (6366*acos(cos(0.853291)*cos(cities.latitude) *
> cos(cities.longitude - (0.040841))+sin(0.853291)*sin(cities.latitude)))
> as km
> FROM proposals
> INNER JOIN users ON proposals.user_id = users.id
> INNER JOIN cities ON proposals.city_id = cities.id
> INNER JOIN categories ON proposals.category_id = categories.id
> INNER JOIN domains ON categories.domain_id = domains.id
> WHERE proposals.city_id IN
> (31556,37294,31547,37302,31548,31555,31546,31557,3 7236,31540,31541,33114,37244,37227,37262)
AND
(6366*acos(cos(0.853291)*cos(cities.latitude)
>
> * cos(cities.longitude-(0.040841))+sin(0.853291)*sin(cities.latitude))) < 25.0
> AND (valid_until >= '2007-01-20 16:37:41' OR valid_until IS NULL)
> ORDER BY km
>
> but if try to run a SELECT COUNT.. I get an the fatal error... :
> Mysql::Error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no
> GROUP columns is illegal if there is no GROUP BY clause
>
> SELECT COUNT(proposals.id) ,
> (6366*acos(cos(0.853291)*cos(cities.latitude) * cos(cities.longitude -
> (0.040841))+sin(0.853291)*sin(cities.latitude))) as km
> FROM proposals
> .....
>
> why should I use a GROUP BY ? and on what ? ('proposals' ?)
>
> thanks for your help
>
> joss

Got it .. adding a GROUP BY NULL is just fine !