MySQL: Isn't in Group By

MySQL : isn't in GROUP BY

You need to have a full group by:

SELECT `name`, `type`, `language`, `code` 
FROM `users` 
WHERE `verified` = '1' 
GROUP BY `name`, `type`, `language`, `code` 
ORDER BY `count` DESC LIMIT 0, 25

SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause. SQL99 loosens this restriction a bit and states that all columns in the select clause must be functionally dependent of the group by clause. MySQL by default allows for partial group by and this may produce non-deterministic answers, example:

create table t (x int, y int);
insert into t (x,y) values (1,1),(1,2),(1,3);
select x,y from t group by x;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
+------+------+

I.e. a random y is select for the group x. One can prevent this behavior by setting @@sql_mode:

set @@sql_mode='ONLY_FULL_GROUP_BY';
select x,y from t group by x; 
ERROR 1055 (42000): 'test.t.y' isn't in GROUP BY

How to resolve isn't in GROUP BY error in mysql query

Each non-aggregated field should be grouped.

It is standard behavior, which in mysql, depends on ONLY_FULL_GROUP_BY mode.

This mode is default enabled in >= 5.7.

select posts.id, post.user_id, count(likings.id) as likes_count
from posts
left join likings on likings.post_id = posts.id and likings.isActive= 1
group by posts.id, posts.user_id
order by likes_count desc 
limit 5

Or, you can aggregate it:

select posts.id, MIN(post.user_id), count(likings.id) as likes_count
from posts
left join likings on likings.post_id = posts.id and likings.isActive= 1
group by posts.id
order by likes_count desc 
limit 5

Other way - change sql_mode:

SET SESSION sql_mode = '';

Also, you can divide it to 2 query:

1. Get aggregate and post ids
2. Get posts data with fetched ids (with IN clause)

isn't in GROUP BY with DAY, MONTH, YEAR

A day by definition is not some free standing abstraction, but also lies in a month and a year. So, you can just select the day, month, and year in your query:

SELECT
    DAY(date_event),
    MONTH(date_event),
    YEAR(date_event),
    COUNT(id) AS howmuch
FROM mytable
WHERE date_event BETWEEN SUBDATE(CURDATE(), 7) AND NOW() 
GROUP BY DAY(date_event), MONTH(date_event), YEAR(date_event) 
ORDER BY YEAR(date_event), MONTH(date_event), DAY(date_event);

But we can make this more concise by just grouping by the DATE(date_event):

SELECT
    DATE(date_event),
    COUNT(id) AS howmuch
FROM mytable
WHERE date_event BETWEEN SUBDATE(CURDATE(), 7) AND NOW() 
GROUP BY DATE(date_event)
ORDER BY DATE(date_event);

sql isn't in GROUP BY using query builder laravel

You are using strict mode in laravel. By the you can tweak it to your needs.

got to config/database.php in set strict=>false and you are good to go.

  'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

GROUP BY statement throwing errors tutorial isn't

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. [1]

You can disable this, but it really makes sense to leave it enabled. Think about the following case:

id | type
---|-----
1  | foo
2  | foo
3  | bar

If you used a similar query to yours, select * from table group by type, what would you expect the output to be? Perhaps you expect two rows, foo and bar, but what do you expect to be in the id column, namely for the foo record? Should we just choose the earliest record? The latest record? A random record? This is what the error is telling you - instead of having the database engine choose for you, you must be explicit.

So, in your example, you need to think about what the resulting data should look like. Maybe instead of select * you want select customers.name, count(*) ..., etc.

The reason this works for the tutorial you're following is either they don't have only_full_group_by enabled or they're using a MySql version older than 5.7.5 where this was introduced. In this case, the server is free to choose any value from each group, which is probably not what you want.

Finally, if you want your console to behave in the same way your tutorial is behaving, you can disable ONLY_FULL_GROUP_BY in the console by:

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

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

MYSQL max() and group by error:only_full_group_by

MySQL isn't able to determine the functional dependence ... between the expressions in the GROUP BY clause, and the expressions in the SELECT list.

The non-aggregate expression in the SELECT list (DATE_FORMAT(date_time, '%H:%i') includes a minutes component. The GROUP BY clause is going to collapse the rows into groups by just hour. So the value of the minutes is indeterminate... we know it's going to come from some row in the group, but there's no guarantee which one.

(The question reference to ONLY_FULL_GROUP_BY seems to indicate that we've got some understanding of indeterminate values...)

The easiest (fewest) changes fix would be to wrap that expression in a MIN or MAX function.

 SELECT MAX(t.counter)                          AS `max`
      , MIN(t.counter)                          AS `min`
      , MIN(DATE_FORMAT(t.date_time,'%H:%i'))   AS `dt`
   FROM table1 t
  WHERE t.date_time >= NOW() - INTERVAL 1 DAY 
  GROUP
     BY YEAR(t.date_time)
      , MONTH(t.date_time)
      , DAY(t.date_time)
      , HOUR(t.date_time)
  ORDER
     BY YEAR(t.date_time)
      , MONTH(t.date_time)
      , DAY(t.date_time)
      , HOUR(t.date_time)

If we want rows returned in a particular order, we should include an ORDER BY clause, and not rely on MySQL-specific extension or behavior of GROUP BY (which may disappear in future releases.)

---

It's a bit odd to be doing a GROUP BY year, month, day and not including those values in the SELECT list. (It's not invalid to do that, just kind of strange. The conditions in the WHERE clause are guaranteeing that we don't have more than 24 hours span for date_time.

My preference would to do the GROUP BY on the same expression as the non-aggregate in the SELECT list. If I ever needed more than 24 hours, I'd include the date component:

 SELECT MAX(t.counter)                             AS `max`
      , MIN(t.counter)                             AS `min`
      , DATE_FORMAT(t.date_time,'%Y-%m-%d %H:00') + INTERVAL 0 DAY AS `dt`
   FROM table1 t
  WHERE t.date_time >= NOW() - INTERVAL 1 DAY 
  GROUP
     BY DATE_FORMAT(t.date_time,'%Y-%m-%d %H:00') + INTERVAL 0 DAY
  ORDER
     BY DATE_FORMAT(t.date_time,'%Y-%m-%d %H:00') + INTERVAL 0 DAY

--or--

if we always know it's just one day's worth of date_time, and we only want to return the hour, then we can group by just the hour. The same expression as in the SELECT list.

 SELECT MAX(t.counter)                             AS `max`
      , MIN(t.counter)                             AS `min`
      , DATE_FORMAT(t.date_time,'%H:00')           AS `dt`
   FROM table1 t
  WHERE t.date_time >= NOW() - INTERVAL 1 DAY 
  GROUP
     BY DATE_FORMAT(t.date_time,'%H:00')
      , DATE_FORMAT(t.date_time,'%Y-%m-%d %H')
  ORDER
     BY DATE_FORMAT(t.date_time,'%Y-%m-%d %H')

Primary key column isn't in GROUP BY

You can do this with a JOIN to the table of MIN values, adding baz = MIN(baz) as a JOIN condition:

SELECT f1.*
FROM foos f1
JOIN (SELECT foo, bar, MIN(baz) AS baz
      FROM foos
      GROUP BY foo, bar) f2 ON f2.foo = f1.foo AND f2.bar = f1.bar AND f2.baz = f1.baz

Demo on dbfiddle

Isn't 'group by' executed after 'select', why use group by 1, 2?

Using the same thing as a select alias and a column name is likely to get you into trouble.

The sale_date returned by the select is just a month; the sale_date column presumably has multiple potential values per month. If you do:

select lower(trim(product_name)) as product_name, date_format (sale_date, '%Y-%m') as sale_date, count(*) as total
from sales
group by product_name, sale_date
order by 1, 2;

it is ambiguous as to whether you mean to group by sale_date the column or sale_date the alias. mysql will assume you mean the column, which will not aggregate your results by month.

Either explicitly repeat the select expression:

group by lower(trim(product_name)), date_format(sale_date, '%Y-%m')

or use the shorthand that allows you to reference select columns:

group by 1, 2

Or distinguish between what you select and what is stored:

select lower(trim(product_name)) as display_product_name, date_format (sale_date, '%Y-%m') as sale_month, count(*) as total
from sales
group by display_product_name, sale_month

---

Related Topics