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.