Need help using SUM function in MySQL

..oO(Dooza)

>AngryCloud wrote:
>> SELECT users.id, users.name, SUM(scores.points) AS user_score FROM users LEFT
>> JOIN scores ON users.id = scores.user_id WHERE user_score > 50
>>
>> [b]How do I make this work?[/b]
>> (query should only return users with sums of points greater than 50)
>>
>
>SELECT users.id, users.name, SUM(scores.points) AS user_score
>FROM users LEFT JOIN scores ON users.id = scores.user_id
>WHERE SUM(scores.points) > 50
>GROUP BY users.id, users.name

That's one way. The other would be a HAVING clause instead of WHERE,
which can also test dynamically created columns from the SELECT part:

SELECT ..., SUM(scores.points) AS user_score
FROM ...
HAVING user_score > 50

Micha
 >> Stay informed about: Need help using SUM function in MySQL 

Michael Fesser wrote:
> ..oO(Dooza)
>
>> AngryCloud wrote:
>>> SELECT users.id, users.name, SUM(scores.points) AS user_score FROM users LEFT
>>> JOIN scores ON users.id = scores.user_id WHERE user_score > 50
>>>
>>> [b]How do I make this work?[/b]
>>> (query should only return users with sums of points greater than 50)
>>>
>> SELECT users.id, users.name, SUM(scores.points) AS user_score
>>FROM users LEFT JOIN scores ON users.id = scores.user_id
>> WHERE SUM(scores.points) > 50
>> GROUP BY users.id, users.name
>
> That's one way. The other would be a HAVING clause instead of WHERE,
> which can also test dynamically created columns from the SELECT part:
>
> SELECT ..., SUM(scores.points) AS user_score
> FROM ...
> HAVING user_score > 50

Yup, saves time processing it too, as your not using the aggregate
function (SUM) twice.

Steve
 >> Stay informed about: Need help using SUM function in MySQL 

I am trying to work with a query structured like this:

SELECT users.id, users.name, SUM(scores.points) AS user_score FROM users LEFT
JOIN scores ON users.id = scores.user_id WHERE users.id = '$var' HAVING
user_score > 50


but I am getting this error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is
illegal if there is no GROUP BY clause
 >> Stay informed about: Need help using SUM function in MySQL 

AngryCloud wrote:
> I am trying to work with a query structured like this:
>
> SELECT users.id, users.name, SUM(scores.points) AS user_score FROM users LEFT
> JOIN scores ON users.id = scores.user_id WHERE users.id = '$var' HAVING
> user_score > 50
>
>
> but I am getting this error:
>
> Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is
> illegal if there is no GROUP BY clause
>

Thats because you haven't grouped it.

Try this:

SELECT users.id, users.name, SUM(scores.points) AS user_score
FROM users LEFT JOIN scores ON users.id = scores.user_id
GROUP BY users.id, users.name
HAVING user_score > 50
 >> Stay informed about: Need help using SUM function in MySQL 

Dooza wrote:
> AngryCloud wrote:
>> I am trying to work with a query structured like this:
>>
>> SELECT users.id, users.name, SUM(scores.points) AS user_score FROM
>> users LEFT JOIN scores ON users.id = scores.user_id WHERE users.id =
>> '$var' HAVING user_score > 50
>>
>>
>> but I am getting this error:
>>
>> Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
>> columns is illegal if there is no GROUP BY clause
>>
>
> Thats because you haven't grouped it.
>
> Try this:
>
> SELECT users.id, users.name, SUM(scores.points) AS user_score
> FROM users LEFT JOIN scores ON users.id = scores.user_id
> GROUP BY users.id, users.name
> HAVING user_score > 50

Actually you want this:

SELECT users.id, users.name, SUM(scores.points) AS user_score
FROM users LEFT JOIN scores ON users.id = scores.user_id
WHERE users.id = '$var'
GROUP BY users.id, users.name
HAVING user_score > 50
 >> Stay informed about: Need help using SUM function in MySQL 

>Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
>columns is illegal if there is no GROUP BY clause

The error message is pretty clear. Anytime you have an aggregate in a select
statement, you need to include non-aggregate expressions in a group by clause.
Otherwise, it doesn't make any logical sense.
 >> Stay informed about: Need help using SUM function in MySQL