MySQL #1140 - Mixing of Group Columns

MySQl Error : #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

If you use any function like sum(),avg(), count()...then by default it will sum/count/avg of all data but if you use any other column based on which you want to categorized your sum/count/avg then you must mention group by after where clause, so your query should be-

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
group by bit_farm.name,bit_regional.name;

MySQL #1140 - Mixing of GROUP columns

The reason a single column using an aggregate function works while the version with columns not using aggregate functions doesn't is because you need to specify a GROUP BY clause. Here's what your query should look resemble:

   SELECT COUNT(n.nid), 
          n.nid, 
          ctu.field_update_date_value
     FROM NODE n
LEFT JOIN CONTENT_TYPE_UPDATE ctu ON ctu.vid = n.vid
    WHERE n.type IN ('update')
 GROUP BY n.nid, ctu.field_update_date_value
 ORDER BY field_update_date_value DESC

I changed out your table aliases for shorter ones - easier to read. Here's the meat of your issue:

   SELECT n.nid,
          COUNT(n.fake_example_column),                
          ctu.field_update_date_value
      ...
 GROUP BY n.nid, ctu.field_update_date_value

I altered the example to use a fake column in order to highlight how the GROUP BY needs to be defined. Any column you reference without wrapping in an aggregate function should to be mentioned in the GROUP BY. I say should because MySQL is the only db I'm aware of that supports a GROUP BY where you can selectively omit columns - there are numerous SO questions about why queries work on MySQL but can't be ported without change to other dbs. Apparently in your case, you still need to define at least one.

Laravel error: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT()

You have to use groupBy():

$feedback_data = DB::table('feedback')
    ->select(DB::raw('office, avg(q1) as q1, avg(q2) as q2'))
    ->groupBy('office')
    ->get();

PDO SQL Bug? Error 1140: Mixing of GROUP columns with no GROUP columns

For future reference, when two connections to the same server are behaving differently, check the mode.

It turns out PDO was running in strict mode and the MySQL REPL was not.

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

Like the error says, you can't SELECT an aggregate function, such as COUNT, without grouping rows either explicitly (using GROUP BY) or implicitly (by just selecting the aggregate). To put it in less technical terms - you're telling the database, "Look up all posts by this username, and the threads they belong to, and the number of posts", and the database is answering you, "the number of posts in what?".

So you'll need to be more specific. If what you actually want is:

• If you don't actually care about all the individual posts, and you just want the threads and the number of posts by this user per thread, remove the p.* from the SELECT, and add GROUP BY t.threadid to the end of the query.
• If you want the total number of posts as well as all the posts and threads, just count the result rows.
• If you want the total number of posts by all users in the threads you're selecting, that'll make the query much more complex, because you'll need to join on post again to get the total post count.
• If you don't actually want the count at at all, remove it from the query. :)

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

SQL92 and earlier does not permit queries for which the select list,
HAVING condition, or ORDER BY list refer to nonaggregated columns that
are neither named in the GROUP BY clause nor are functionally
dependent on (uniquely determined by) GROUP BY columns. For example,
this query is illegal in standard SQL92 because the nonaggregated name
column in the select list does not appear in the GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
GROUP BY o.custid;

For the query to
be legal in SQL92, the name column must be omitted from the select
list or named in the GROUP BY clause.

SQL99 and later permits such nonaggregates per optional feature T301
if they are functionally dependent on GROUP BY columns: If such a
relationship exists between name and custid, the query is legal. This
would be the case, for example, were custid a primary key of
customers.

MySQL 5.7.5 and up implements detection of functional dependence. If
the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default),
MySQL rejects queries for which the select list, HAVING condition, or
ORDER BY list refer to nonaggregated columns that are neither named in
the GROUP BY clause nor are functionally dependent on them. (Before
5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of
pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

When there's no group by there are no columns for a functional dependence. You probably want a subquery to grab the value of min(timeinmilliseconds).

---

Related Topics