Why does GROUP BY not work with MySQL in this case?

The example is not very good because it is not conceptually suitable for grouping.

You want me to show you just once each occupation. So far so good, normal. He wants me to show the name of someone who has this occupation. But there are several people, what to do with those different names? Show only the first? Is this information useful? I doubt that. So the query does not make sense.

The standard treatment is to consider this an error. You can only select columns that are in the group definition, so all information to be displayed will only exist once. Or you can use a aggregation function in the column, so the function combines the various results in one and allows you to present in the grouping.

One of the most commonly used aggregation functions is SUM() that sums all values. In a character column you can not, you'll probably want to count how many people you have in the group ( COUNT() ). If you do not want to add anything you have to remove the column name from the query.

One way to get some value is ANY_VALUE() , but I disagree with the reasons already mentioned.

Documentation .

As the error indicates, the SELECT statement contains columns that are not part of the GROUP BY clause and at the same time are not aggregated (through an aggregate function).

The GROUP BY aggregation clause, when applied to a query, divides the result set into groups, according to the indicated columns, in order to apply one or more (aggregate functions) to each of the groups. When the GROUP BY statement is used, SELECT will return only one result row for each of the groups.

Given the definition and its example, we have a problem:

In your occupation table, there is more than Name for each occupation . By not applying an aggregate function in the Name column, but being included in the SELECT list, the DBMS can not determine which information to display for each of the occupation , for example, what should be Name associated with Professor (there is more than one possibility in his table).

To correct the error, or remove the Name column from the SELECT statement (which does not make much sense because it would be the same as SELECT DISTINCT or apply an aggregate function, COUNT .

SELECT occupation,
       COUNT(name) 
  FROM OCCUPATIONS 
 GROUP BY occupation; 

The preceding statement returns the following result:

Doctor, N1 
Professor, N2  
Singer, N3 
Actor N4

You can use the DISTINCT statement. It works basically as a GROUP BY of all fields, eliminating duplicate records, but without any aggregation function, as is the case.