Why doesn't this select work?

Question

SQL

Hello everyone!
Tell me why this select does not work?

SELECT field1, count(field1), field2
  FROM [Table] 
  group by field1
  having count(field1)>1
  order by count(field1) desc

Answer 1

What a strange request you have. try this:

SELECT field1, count(field1) as countf1, field2
  FROM [Table] 
  group by field1, field2
  having countf1>1
  order by field1 desc

Answer 2

Since you did not provide the text of the error, I will vangue.
Field2 is not included in either GROUP BY or aggregate function. This means that there is an ambiguous choice, from which row, which has fallen into the group, to take this field.
According to the ANSI SQL standard, this use of the field is prohibited.

Answer 3

SELECT field1, COUNT( field1 ), STRING_AGG( field2, ',' )
  FROM [Table] 
  GROUP BY field1
  HAVING COUNT( field1 ) > 1
  ORDER BY COUNT( field1 ) DESC