1

The following query is done after disabling ONLY_FULL_GROUP_BY in MySQL. Now I want the same result without disabling ONLY_FULL_GROUP_BY mode like GROUP BY r.user_id, u.fullname, r.time_taken

SELECT u.fullname, ROUND(AVG(r.correct), 2) avg_correct, date_format(r.time_taken,'%d-%m-%Y') time_taken
FROM (SELECT user_id, concat( first_name, ' ', last_name) fullname from user) u
LEFT JOIN test_result r ON u.user_id = r.user_id
GROUP BY r.user_id
ORDER BY r.time_taken DESC

Screenshot: screenshot

Can anyone help me?

w3outlook
  • 823
  • 6
  • 16
  • Values for r.correct and r.time_taken are arbitrary. You have to define a logic which value to choose MIN/MAX/.... Related [Group by clause in mySQL and postgreSQL, why the error in postgreSQL?](https://stackoverflow.com/a/33629201/5070879) – Lukasz Szozda Apr 14 '19 at 10:50

2 Answers2

3

I would recommend writing this query as:

SELECT CONCAT(u.first_name, ' ', u.last_name) as fullname,
       ROUND(AVG(r.correct), 2) as avg_correct,  
       DATE_FORMAT(MAX(r.time_taken), '%d-%m-%Y') as time_taken
FROM user u LEFT JOIN
     test_result r
     ON u.user_id = r.user_id
GROUP BY u.user_id, fullname
ORDER BY MAX(r.time_taken) DESC;

Notes:

  • The subquery in the FROM clause does not help the query. It might impede the optimizer.
  • Don't GROUP BY columns from the second table in a LEFT JOIN (unless you really know what you are doing). The value would be NULL for non-matches.
  • MySQL and MariaDB allow column aliases in the GROUP BY clause.
  • For the ORDER BY to work as you intend, it needs to be on the value before formatting, not after formatting. The format %d-%m-%Y does not order by time.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

You must add u.fullname to the group by clause and this does not affect the results as the user's id and fullname are uniquely grouped together, but in the case of time_taken you must use any_value():

SELECT 
  u.fullname, 
  ROUND(AVG(r.correct), 2) avg_correct, 
  date_format(any_value(r.time_taken),'%d-%m-%Y') time_taken
FROM (SELECT user_id, concat( first_name, ' ', last_name) fullname from user) u
LEFT JOIN test_result r ON u.user_id = r.user_id
GROUP BY r.user_id, u.fullname
ORDER BY time_taken DESC

You can find more here: MySQL Handling of GROUP BY

forpas
  • 160,666
  • 10
  • 38
  • 76