Fix ERROR 1140 “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column…” in MySQL

If you’re getting MySQL error 1140, which reads something like “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘world.City.District’; this is incompatible with sql_mode=only_full_group_by“, it could be that you need to introduce a window function to the query.

This error can happen when we want multiple rows to contain aggregate values. For example, we might want to return subtotals of all rows within a group of rows. A window function can help us achieve this outcome.

Example of Error

Here’s an example of code that produces the error:

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

Result:

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'world.City.District'; this is incompatible with sql_mode=only_full_group_by

Here, the last column in my SELECT list is causing the problem. I’m using the SUM() aggregate function to try to calculate the population of all cities within their respective districts.

The problem is, my query is incomplete. It doesn’t actually specify that the SUM() function is for all cities in the district. I need to introduce a window function to fix this query.

Solution

As mentioned, we can fix the query with a window function:

SELECT 
    District, 
    Name AS "City Name", 
    Population AS "City Population", 
    SUM(Population) OVER(PARTITION BY District) AS "District Population"
FROM City 
WHERE CountryCode = 'AUS' 
ORDER BY District, Name, "City Population";

Result:

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

All I did was add an OVER() clause. Specifically, I added OVER(PARTITION BY District) to the last column.

The OVER() clause creates a window function. This allows us to get the population of all cities within each district. We achieved this by telling it to partition the results of the SUM() function by district (i.e. PARTITION BY District). The end result is that we get a full list of cities, along with the total population of their respective districts.

Another (Half?) Solution

Another (perhaps, less desirable) way to deal with the problem is to remove only_full_group_by from our SQL mode. The error message told us that our code is incompatible with sql_mode=only_full_group_by, and so it might be tempting to remove only_full_group_by from our SQL mode.

However, depending on what you’re trying to do with your query, this may or may not provide the desired result.

To demonstrate, here’s how we can remove only_full_group_by from our SQL mode:

SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');

Now when we run the problematic code, we don’t get an error:

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

Result:

+-----------------+-----------+-----------------+---------------------+
| District        | City Name | City Population | District Population |
+-----------------+-----------+-----------------+---------------------+
| New South Wales | Sydney    |         3276207 |            11313666 |
+-----------------+-----------+-----------------+---------------------+
1 row in set (0.00 sec)

However, in my case the result is not what I wanted. Only one row is returned and I get the full aggregate population of the whole country instead of each district.