6 Ways to Fix Error 1055 “Expression … of SELECT list is not in GROUP BY clause and contains nonaggregated column…” in MySQL

If you’ve been using MySQL for any decent amount of time, it’s likely you’ll be familiar with error 1055 that reads something like “Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column…“, where #N is the expression number of an expression/column in your SELECT list.

This error can occur when we include a column in the SELECT list, but we omit it from the GROUP BY clause.

There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.

Example of Error

First, here’s an example of code that produces the error:

SELECT 
    District, 
    Name, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

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

Here, #2 means that the second expression in our SELECT list is causing the problem. In our case, it’s the Name column. We included the Name column in our SELECT list but not the GROUP BY clause.

This is a common error which occurs when our SELECT list includes a nonaggregated column that is neither named among GROUP BY columns nor functionally dependent on them.

There are several ways of dealing with this. The method we choose will depend on our desired result.

Solution 1

The most obvious way to deal with this error is to simply remove the offending column:

SELECT 
    District, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-----------------+
| District        | SUM(Population) |
+-----------------+-----------------+
| New South Wales |         3993949 |
| Victoria        |         2990711 |
| Queensland      |         1805236 |
| West Australia  |         1096829 |
| South Australia |          978100 |
| Capital Region  |          322723 |
| Tasmania        |          126118 |
+-----------------+-----------------+

However, this will only be suitable if we aren’t concerned with returning that column.

If we do want that column to be included in the result, we can look at the solutions below.

Solution 2

Another way to deal with this issue is to include the Name column in the GROUP BY clause:

SELECT 
    District, 
    Name, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District, Name;

Result:

+-----------------+---------------+-----------------+
| District        | Name          | SUM(Population) |
+-----------------+---------------+-----------------+
| New South Wales | Sydney        |         3276207 |
| Victoria        | Melbourne     |         2865329 |
| Queensland      | Brisbane      |         1291117 |
| West Australia  | Perth         |         1096829 |
| South Australia | Adelaide      |          978100 |
| Capital Region  | Canberra      |          322723 |
| Queensland      | Gold Coast    |          311932 |
| New South Wales | Newcastle     |          270324 |
| New South Wales | Central Coast |          227657 |
| New South Wales | Wollongong    |          219761 |
| Tasmania        | Hobart        |          126118 |
| Victoria        | Geelong       |          125382 |
| Queensland      | Townsville    |          109914 |
| Queensland      | Cairns        |           92273 |
+-----------------+---------------+-----------------+

However, this may or may not return the results we want. If this isn’t our desired result, we can try one of the other solutions below.

In my case, it’s not the outcome I was looking for. In fact, it defeated the purpose of grouping by district (I wanted to see the total population of each district, but it’s only displaying the population of each city).

Solution 3

Another way to deal with this error is with the ANY_VALUE() function.

SELECT 
    District, 
    ANY_VALUE(Name), 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-----------------+-----------------+
| District        | ANY_VALUE(Name) | SUM(Population) |
+-----------------+-----------------+-----------------+
| New South Wales | Sydney          |         3993949 |
| Victoria        | Melbourne       |         2990711 |
| Queensland      | Brisbane        |         1805236 |
| West Australia  | Perth           |         1096829 |
| South Australia | Adelaide        |          978100 |
| Capital Region  | Canberra        |          322723 |
| Tasmania        | Hobart          |          126118 |
+-----------------+-----------------+-----------------+

The ANY_VALUE() function picks an arbitrary value from the specified column. In this case, it picked an arbitrary value from the Name column, and we got Sydney in the first row, Melbourne in the second, and so on. As we can see from the previous example, that column contains more than just those values for their respective districts, but MySQL has simply chosen one value to present.

This method is good if we’re not worried about which value is returned for that column. For example, if we just want a sample value in that column, but we don’t want to see all values.

However, if we want to see all values, we could try the next solution.

Solution 4

Another option is to use the GROUP_CONCAT() function:

SELECT 
    District, 
    GROUP_CONCAT(Name), 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-------------------------------------------+-----------------+
| District        | GROUP_CONCAT(Name)                        | SUM(Population) |
+-----------------+-------------------------------------------+-----------------+
| Capital Region  | Canberra                                  |          322723 |
| New South Wales | Sydney,Newcastle,Central Coast,Wollongong |         3993949 |
| Queensland      | Brisbane,Gold Coast,Townsville,Cairns     |         1805236 |
| South Australia | Adelaide                                  |          978100 |
| Tasmania        | Hobart                                    |          126118 |
| Victoria        | Melbourne,Geelong                         |         2990711 |
| West Australia  | Perth                                     |         1096829 |
+-----------------+-------------------------------------------+-----------------+

This option is similar to the ANY_VALUE() solution, except that GROUP_CONCAT() returns all values in a comma-separated list.

The good thing about this is that we don’t need to output a separate row for each city. Each city is output in the same column. So we can retain the grouping by District that we intended, while also getting all data from the Name column.

Solution 5

There are other aggregate functions that we could use to get rid of the error, depending on what data we want returned. For example, we could use the MIN() or MAX() function instead of the ANY_VALUE() function:

SELECT 
    District, 
    MIN(Name), 
    MAX(Name), 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+---------------+------------+-----------------+
| District        | MIN(Name)     | MAX(Name)  | SUM(Population) |
+-----------------+---------------+------------+-----------------+
| New South Wales | Central Coast | Wollongong |         3993949 |
| Victoria        | Geelong       | Melbourne  |         2990711 |
| Queensland      | Brisbane      | Townsville |         1805236 |
| West Australia  | Perth         | Perth      |         1096829 |
| South Australia | Adelaide      | Adelaide   |          978100 |
| Capital Region  | Canberra      | Canberra   |          322723 |
| Tasmania        | Hobart        | Hobart     |          126118 |
+-----------------+---------------+------------+-----------------+

This can be handy to give us an idea of the range of values in the column without returning all values.

Solution 6

We only get the error when our sql_mode contains ONLY_FULL_GROUP_BY.

We can check our sql_mode like this:

SELECT @@sql_mode;

Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

As expected, ONLY_FULL_GROUP_BY is enabled, which is why I got the error.

We can disable ONLY_FULL_GROUP_BY from our session’s sql_mode like this:

SET @@sql_mode = SYS.LIST_DROP(@@sql_mode, 'ONLY_FULL_GROUP_BY');
SELECT @@sql_mode;

Result:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

By using the SYS.LIST_DROP system function, we removed the item from the list without affecting any other items. We can see that ONLY_FULL_GROUP_BY is no longer in our sql_mode.

Let’s now run the original query that caused the error:

SELECT 
    District, 
    Name, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-----------+-----------------+
| District        | Name      | SUM(Population) |
+-----------------+-----------+-----------------+
| New South Wales | Sydney    |         3993949 |
| Victoria        | Melbourne |         2990711 |
| Queensland      | Brisbane  |         1805236 |
| West Australia  | Perth     |         1096829 |
| South Australia | Adelaide  |          978100 |
| Capital Region  | Canberra  |          322723 |
| Tasmania        | Hobart    |          126118 |
+-----------------+-----------+-----------------+

This time the query runs without error. Our results resemble those we got when we used the ANY_VALUE() function.

Conclusion

MySQL error 1055 is a common error that occurs when using the GROUP BY clause. It occurs when we include a column in our SELECT list that’s not also included in the GROUP BY clause.

However, there are many simple ways of dealing with this error. The solution we chose will depend on the actual results that we want to see from the query.