Mysql error code 1111 invalid use of group function - Исправление ошибок и поиск оптимальных решений проблем

When executing a query in MySQL, you may encounter an error saying Invalid use of group function when using aggregate functions like AVG(), SUM(), MAX(), MIN(), and many others.

For example, suppose you have a table named pets that keep the following records:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
|  3 | Joe    | horse   |    4 |
|  4 | Mark   | dog     |    4 |
|  5 | Peter  | dog     |    5 |
+----+--------+---------+------+

From this table, you are required to query the table and show rows where the age value is smaller than the average age of all rows.

You may write a SELECT statement as follows, which triggers the Invalid use of group function error:

mysql> SELECT * FROM pets WHERE age < AVG(age);

ERROR 1111 (HY000): Invalid use of group function

The error above is because the AVG() function is used inside the WHERE clause.

Aggregate functions like AVG(), COUNT(), MAX(), MIN(), and many others can’t be used in the WHERE() clause

There are two ways you can solve this error in MySQL:

Wrap the aggregate function call in a subquery
Use the HAVING clause for the aggregate function call

This tutorial will help you learn how to do both. Let’s start with using the HAVING clause

Fix invalid use of group function with a subquery

When you need to use an aggregate function inside a WHERE clause, you need to wrap the aggregate function call in a subquery.

Returning to the pets table, you can fix the query from this:

SELECT * FROM pets WHERE age < AVG(age);

To this:

SELECT * FROM pets WHERE age < (SELECT AVG(age) FROM pets);

The average age value is 3.6, so the result set will only return rows where age is smaller than that:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
+----+--------+---------+------+

That’s one way you can fix the invalid use of group function error. Next, let’s see how to fix the error using a HAVING clause.

Fix invalid use of group function with HAVING clause

From the same pets table above, suppose you want to find out the average age of the pets and show only pets where the average age value is greater than 2.

You may write a SELECT statement as follows:

SELECT species, AVG(age) FROM pets WHERE AVG(age) > 2 GROUP BY species;

But the query above throws the same error because aggregate functions can’t be used in the WHERE clause.

Instead of using the WHERE clause, you can use the HAVING clause as follows:

SELECT species, AVG(age) FROM pets GROUP BY species HAVING AVG(age) > 2;

Now the query should work and returns the correct result.

Conclusion

The MySQL error Invalid use of group function is caused by aggregate functions in your query that’s placed in the wrong clause.

Most likely you are placing one or more aggregate functions inside the WHERE clause, which won’t work because the WHERE clause filters the table before MySQL actually does the computation.

When you’re using a WHERE clause, the SQL query works like this:

Filter the rows using the WHERE clause
Compute the aggregate functions call

When MySQL runs the WHERE clause, the computation of the aggregate functions hasn’t been executed yet, so it throws an error.

When you’re using a subquery MySQL will evaluate the subquery first, so the average age value in the pets table above will be computed before selecting the rows with the WHERE clause.

Finally, the HAVING clause works like the WHERE clause, but it’s executed AFTER the computation has been done:

Compute the aggregate functions call
Filter the rows using the HAVING clause

This is why the HAVING clause can have aggregate functions while the WHERE clause can’t.

You’ve just learned how to fix the Invalid use of group function error. Nice work! 👍

Источник

27 июля 2009 г.

MySQL

Не возможно использовать для обновления таблицу, в которой производишь выборку

Понадобилось ежесуточно высчитывать размер шрифта для оформления вывода жанров на Кинсбурге. И столкнулся с проблемой, что нельзя выбирать данные из таблицы, которая участвует в обновлении данных.

Вот примеры запросов

Пробуем в лоб:

update `categories` set `size` = (`count` / ((max(`count`) - min(`count`)) / 10));
ERROR 1111 (HY000): Invalid use of group function

Попробуем вложенный селект:
update `categories` set `size` = (select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`);
ERROR 1093 (HY000): You can't specify target table 'categories' for update in FROM clause

Попробуем жоин:
update `categories` as `c1` 
JOIN `categories` as `c2` 
using(`category_id`) 
set `c1`.`size` = (`c2`.`count` / ((max(`c2`.`count`) - min(`c2`.`count`)) / 10));

ERROR 1111 (HY000): Invalid use of group function

По отдельности все работает
Выводим «размер шрифта»:
select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`;
+--------------------------------------------------------+
| (`count` / ((max(`count`) - min(`count`)) / 10))     |
+--------------------------------------------------------+
|                                             3.47826087 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Обновляем поле с размером шрифта:
update `categories` set `size` = 3.47826087;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 21  Changed: 0  Warnings: 0

Решение
Погуглив, и поломав голову с disc’ом, я пришел к следующему решению, представленное ниже.
Я решил выделить вычисление процента в переменную @percent, далее создал вьюху для таблицы «categories» и жойню таблицу с вьюхой:
-- создаем коэффициент деления
set @percent = (select (max(`count`) - min(`count`)) / 10 from `categories`);

-- создаем вьюху
create view `categories_view` as select `category_id`, `count` from `categories`;

-- жойним таблицу и вьюху, обновляя данные
update `categories` as `c`
join `categories_view` as `cv`
using(`category_id`)
set `c`.`size` = `cv`.`count` / @percent;

Вот и все, приятного манокурения   
UPD: Создадим процедуру и евент для этого события
/* Создаем вьюху и процедуру для установки размеров шрифта */
use kinsburg;

/* создаем вьюху */
CREATE VIEW `categories_view` AS SELECT `category_id`, `count` FROM `categories`;

/* создаем процедуру */
delimiter //
DROP PROCEDURE IF EXISTS `updateCategorySize`//
CREATE PROCEDURE `updateCategorySize` ()
BEGIN
    /* создаем коэффициент деления */
    SET @percent = (SELECT (max(`count`) - min(`count`)) / 10 FROM `categories`);
    /* жойним таблицу и вьюху, обновляя данные */
    UPDATE `categories` AS `c` JOIN `categories_view` AS `cv` USING(`category_id`) SET `c`.`size` = `cv`.`count` / @percent;
END//
delimiter ;

/* создаем евент для вызова процедуры раз в сутки */
CREATE
    DEFINER = kinsburg@localhost
    EVENT `updateCategorySizeEvent`
    ON SCHEDULE
      EVERY 1 DAY
    DO
      CALL updateCategorySize;

Источник

To correctly use aggregate function with where clause in MySQL, the following is the syntax −

select *from yourTableName
where yourColumnName > (select AVG(yourColumnName) from yourTableName);

To understand the above concept, let us create a table. The query to create a table is as follows −

mysql> create table EmployeeInformation
   -> (
   -> EmployeeId int,
   -> EmployeeName varchar(20),
   -> EmployeeSalary int,
   -> EmployeeDateOfBirth datetime
   -> );
Query OK, 0 rows affected (1.08 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into EmployeeInformation values(101,'John',5510,'1995-01-21');
Query OK, 1 row affected (0.13 sec)
mysql> insert into EmployeeInformation values(102,'Carol',5600,'1992-03-25');
Query OK, 1 row affected (0.56 sec)
mysql> insert into EmployeeInformation values(103,'Mike',5680,'1991-12-25');
Query OK, 1 row affected (0.14 sec)
mysql> insert into EmployeeInformation values(104,'David',6000,'1991-12-25');
Query OK, 1 row affected (0.23 sec)
mysql> insert into EmployeeInformation values(105,'Bob',7500,'1993-11-26');
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from EmployeeInformation;

The following is the output −

+------------+--------------+----------------+---------------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |
+------------+--------------+----------------+---------------------+
|        101 | John         |           5510 | 1995-01-21 00:00:00 |
|        102 | Carol        |           5600 | 1992-03-25 00:00:00 |
|        103 | Mike         |           5680 | 1991-12-25 00:00:00 |
|        104 | David        |           6000 | 1991-12-25 00:00:00 |
|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |
+------------+--------------+----------------+---------------------+
5 rows in set (0.00 sec)

Here is the correct way to use aggregate with where clause. The query is as follows −

mysql> select *from EmployeeInformation
   -> where EmployeeSalary > (select AVG(EmployeeSalary) from EmployeeInformation);

The following is the output −

+------------+--------------+----------------+---------------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |
+------------+--------------+----------------+---------------------+
|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |
+------------+--------------+----------------+---------------------+
1 row in set (0.04 sec)

Источник