Nonaggregated column of sql_mode=ONLY_FULL_GROUP_BY of mysql

Question

The SQL got some problem with sql_mode = ONLY_FULL_GROUP_BY how to rewrite this SQL ?

SELECT
    s.id,
    s.users_id,
    COUNT(*) as attempt,
    s.time
    s.language,
    s.is_public,
    u.name,
    u.nick
FROM
    s
INNER JOIN users u ON u.id = s.users_id
WHERE s.sid = 10 AND s.result = 1
GROUP BY s.users_id
ORDER BY s.time

Answer 1

if your are using mysql 5.7 or above you can try this

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Answer 2

Two issues. One, you're missing a comma in the column list, and Two, with that setting, you have to include all non-summary columns in your group by:

SELECT
    s.id,
    s.users_id,
    COUNT(*) as attempt,
    s.time,
    s.language,
    s.is_public,
    u.name,
    u.nick
FROM
    s
INNER JOIN users u ON u.id = s.users_id
WHERE s.sid = 10 AND s.result = 1
GROUP BY s.id,
    s.users_id,
    s.time,
    s.language,
    s.is_public,
    u.name,
    u.nick
ORDER BY s.time

Answer 3

I suspect that you want:

SELECT u.id, u.name, u.nick, COUNT(*) as attempt,
       MIN(s.time) as time  -- maybe you want MAX()
FROM s INNER JOIN
     users u
     ON u.id = s.users_id
WHERE s.sid = 10 AND s.result = 1
GROUP BY u.id
ORDER BY MIN(s.time);