CODEWITHSUNDEEP
javamysqlspringhibernateh2
Karol
Karol

Reputation: 99

H2 database: Column "U" must be in the GROUP BY list; SQL statement

My native query looks like this:

SELECT u
FROM (SELECT max(difficulty), u
      FROM (SELECT sum(difficulty) AS difficulty, username AS u
            FROM user_answer ua
                     JOIN question q ON ua.question_id = q.id
                     JOIN answer a ON ua.answer_id = a.id
                     JOIN user_quiz uq ON ua.user_quiz_id = uq.id
                     JOIN account ac ON uq.account_id = ac.id
            WHERE a.correct = 1
              AND uq.quiz_start_date_time >= (CURDATE() - INTERVAL 7 DAY)
            GROUP BY ua.user_quiz_id
            ORDER BY difficulty DESC) AS max_week) as d_u

Everything works fine in MySQL. I have an error in the H2 database:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "U" must be in the GROUP BY list; SQL statement:
SELECT u FROM (SELECT max(difficulty), u FROM (SELECT sum(difficulty) AS difficulty, username AS u FROM user_answer ua JOIN question q ON ua.question_id = q.id JOIN answer a ON ua.answer_id = a.id JOIN user_quiz uq ON ua.user_quiz_id = uq.id JOIN account ac ON uq.account_id = ac.id WHERE a.correct = 1 AND uq.quiz_start_date_time >= (CURDATE() - INTERVAL 7 DAY) GROUP BY ua.user_quiz_id ORDER BY difficulty DESC) AS max_week) as d_u [90016-200]

how to fix it?

Upvotes: 3

Views: 2528

Answers (1)

nbk
nbk

Reputation: 49375

Simply add an aggregation function around username

    SELECT u
    FROM (SELECT max(difficulty), u
          FROM (SELECT sum(difficulty) AS difficulty, MAX(username) AS u
                FROM user_answer ua
                         JOIN question q ON ua.question_id = q.id
                         JOIN answer a ON ua.answer_id = a.id
                         JOIN user_quiz uq ON ua.user_quiz_id = uq.id
                         JOIN account ac ON uq.account_id = ac.id
                WHERE a.correct = 1
                  AND uq.quiz_start_date_time >= (CURDATE() - INTERVAL 1 MONTH)
                GROUP BY ua.user_quiz_id
                ORDER BY difficulty DESC
            ) AS max_month
        GROUP BY u
        ) as d_u

When the option ONLY_FULL_GROUP_BY is enabled you need to have all columns with aggregation functions or have them in the GROUP By see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Upvotes: 1

Related Questions