0

enter image description here

The question is to return the duplicate rows from the table above.

My query is

SELECT *
FROM users
HAVING COUNT(*) > 1

The answer is

SELECT *
FROM users
GROUP BY id, name, created_at
HAVING COUNT(*) > 1

What is the difference between those 2 above?

I thought the COUNT aggregation is based on the all of the columns so I didn't add the "group by".

nbk
  • 45,398
  • 8
  • 30
  • 47
소이소이
  • 11
  • 2
  • Ensure that titles focus the question “what is the difference” is usually not refined enough. – user2864740 Jan 29 '22 at 23:05
  • The such a focused title can be used to search for similar queries (that answer the core question): https://stackoverflow.com/q/6924896/2864740 , https://dba.stackexchange.com/q/57445 – user2864740 Jan 29 '22 at 23:07
  • You need to say *what* is having a count > 1, which is what the group-by does. – Stu Jan 29 '22 at 23:08
  • 1
    @user2864740 I know MySql [can disregard the rules](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by) when it comes to group by. – Stu Jan 29 '22 at 23:10
  • I'm not saying the query is illegal or an error - the 2nd query though has clear and obvious intent. – Stu Jan 29 '22 at 23:17
  • 1
    on a system with full group by the first query would run https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4e35d1da9854dd91473e5908e21dc520 – nbk Jan 29 '22 at 23:29

0 Answers0