Error on sql statement

Question

SELECT AVG(totalled) as mySum , `id` 
FROM (
        SELECT totalled,id 
        FROM `figure` 
        where `userid`='".$userid."' 
        ORDER BY id DESC 
        LIMIT 4
    ) t1

I've created this statement but the error I am getting is: Static analysis:

3 errors were found during analysis.

An expression was expected. (near "(" at position 42) Unexpected token. (near "(" at position 42) This type of clause was previously parsed. (near "SELECT" at position 43) SQL query: Documentation

SELECT AVG(totalled) as mySum , `id` 
FROM (
        SELECT totalled,id 
        FROM `figure` 
        where `userid`='".$userid."' 
        ORDER BY id DESC 
        LIMIT 4
    ) t1

MySQL said: Documentation

1140 - In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 't1.id'; this is incompatible with sql_mode=only_full_group_by

What would I have to do to fix this problem.

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jun 23 '17 at 10:49
why give alias t1, even if given use AVG(t1.totalled) as mySum , `t1.id` — Krunal Limbad, Jun 23 '17 at 10:52

score 0 · Answer 1 · answered Jun 23 '17 at 11:00

Yuo have a column (id) that is not in group by so the db engine raise an error. this happen starting from mysql 5.7

SELECT AVG(totalled) as mySum , `id` 
FROM (
    SELECT totalled,id 
    FROM `figure` 
    where `userid`='".$userid."' 
    ORDER BY id DESC 
    LIMIT 4
 ) t1
group by `id`

Error on sql statement

1 Answers1