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

Question

I am using the Mysql language and am trying to group a table of professions with the GROUP BY through the consultation below.

SELECT name, occupation FROM OCCUPATIONS GROUP BY occupation;

But I get this mistake when trying to group the professions.

ERROR 1055 (42000) at line 2: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_bcxfn77ibag.OCCUPATIONS.Name' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The table is very simple and has this format

The table has the columns name and occupation.

I understood that the error says something about the record is on formed list.

But when making a brief consultation of the professions, it returns me this:

Why does this happen?

Answer 1

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

You want to show only once each occupation. So far so good, normal. You want to show the name of someone who has this occupation. But there are several people, what to do with these different names? Shows only the first? Is this useful information? I doubt. So the consultation doesn’t make sense.

Standard treatment is to consider this a mistake. You can only select columns that are in the grouping definition, then really all information to be shown will only exist once. Or you can use a aggregation function in the column, thus the function combines the various results in one and allows to present in the grouping.

One of the most commonly used aggregation functions is the SUM() adding up all the values. In a column of characters you can’t do this, you probably want to count how many people you have in the group (COUNT()). If you don’t want to add anything you have to remove the column name of consultation.

You can even turn off this restriction in Mysql. But it is not the case since it will bring wrong results once the concept is wrong.

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

Documentation.

Answer 2

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

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

Given the definition and its example, we are facing a problem:

On your table occupation, there is more than one Name for each occupation. By not applying an aggregation function in the column Name, but this is included in the list of SELECT, DBMS cannot determine what information to show for each of the occupation, for example, what should be the Name associated to Professor (in your table there is more than one possibility).

To correct the error, or remove the column Name of the instruction of SELECT (thing that doesn’t make much sense, because it would be the same as a SELECT DISTINCT or applies an aggregation function to it, for example, COUNT.

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

The previous statement returns the following result:

Doctor, N1 
Professor, N2  
Singer, N3 
Actor N4

Answer 3

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