Ошибка 1140 sql

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.

Answer by Hallie Jaramillo

Example: Error Code: 1140. In aggregated query without GROUP BY, expression #3 of SELECT list contains nonaggregated column ‘grepper_history.search_history.term’; this is incompatible with sql_mode=only_full_group_by

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Answer by Isabel Foley

2

count() is an aggregation function in mysql. It can only be used when attempting to aggregate the values of a column across all rows. It cannot be used along with getting other values on a per row basis as you are attempting. You can simple count the results returned if you need a count.

– coderodour

Apr 18 ’17 at 20:46

,Why am I getting this error now and what do I do to resolve it painlessly.,You can change the MySQL settings to default to the old behavior to allow not-so-great queries like this. Information can be found here,Find centralized, trusted content and collaborate around the technologies you use most.

Option 2 would look something like:

SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1

Answer by Florence Hamilton

you should have to use group by id in the end of this to get it work, Please sign in or create an account to participate in this conversation. ,Maulayyacyber started this conversation 1 year ago. 1 person has replied. , Hey! If you have a code-related question, please instead use the forum. Or, if you’d like to sign up with Paypal, though we don’t officially offer that option on the sign up page, you can manually pay for a year’s subscription ($99) here , and we’ll set you up right away!

Hi, im try use make this eloquent

$surat_masuk = DB::table('history_surat')
            ->addSelect(DB::raw('COUNT(id) as jumlah'))
            ->addSelect(DB::raw('MONTH(created_at) as bulan'))
            ->get();

but im trouble

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'db_surat_desa.history_surat.created_at'; this is incompatible with sql_mode=only_full_group_by (SQL: select COUNT(id) as jumlah, MONTH(created_at) as bulan from `history_surat`)

Answer by Ali Palacios

this is incompatible with sql_mode=only_full_group_by,
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
,
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
,
mysql only_full_group_by

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Answer by Samara Delacruz

This query seems to work perfect on my older machine. However, on my new machine with MySQL 5.7.14 and PHP 5.6.25 it seems to throw an error:,Fatal error: Uncaught exception ‘PDOException’ with message
‘SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated
query without GROUP BY, expression #1 of SELECT list contains
nonaggregated column ‘pixel_perfect.users.id’; this is incompatible
with sql_mode=only_full_group_by’ in C:\wamp64\www,A change was made in version 5.7-ish where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board. ,Change ur SQL mode to default.. it will execute without error
The SQL mode defines the syntax of the query. If you using ONLY_FULLY_GROUPBY you have to write a query with group by for all aggregator functions

Here is what my query looks like:

$sql="SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email LIMIT 1";

$stmt=$db->prepare($sql);
$stmt->bindValue(':email', $email);
$stmt->execute();

I have a query to calculate sum of each column from a table selecting sum and column values.

    $query = "select sum(salleryallowance), 
sum(entertainmentexp),
sum(depreciation), 
sum(electricity), 
sum(securitygard),
sum(machinaryrepaire), 
sum(totalrepairing),
sum(othermaintanaice),
sum(postal_charge),
sum(officeexp), 
sum(stationary),
sum(rent_lease_thresher), 
sum(rent_tractor), 
sum(traivlingallowance),
sum(transportaion_cost), 
sum(bank_commition), 
sum(total_exp),
sum(interest_earned), 
bit_farm.name as fname, 
bit_regional.name as rname 
from bit_income_expenditure 
inner join bit_farm on bit_income_expenditure.farm_id = bit_farm.id 
inner join bit_regional on bit_income_expenditure.region_id = bit_regional.id
";
$fetch = mysql_query($query);

$row = mysql_fetch_array($fetch);

// and print my output

echo $row[0]; //and so on....

When I execute this code, it shows following error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

How can I solve this error?