I am using MySQL. Here is my schema:
Suppliers(sid: integer, sname: string, address string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
(primary keys are bolded)
I am trying to write a query to select all parts that are made by at least two suppliers:
-- Find the pids of parts supplied by at least two different suppliers.
SELECT c1.pid -- select the pid
FROM Catalog AS c1 -- from the Catalog table
WHERE c1.pid IN ( -- where that pid is in the set:
SELECT c2.pid -- of pids
FROM Catalog AS c2 -- from catalog
WHERE c2.pid = c1.pid AND COUNT(c2.sid) >= 2 -- where there are at least two corresponding sids
);
First off, am I even going about this the right way?
Secondly, I get this error:
1111 — Invalid use of group function
What am I doing wrong?
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! 👍
I am using the following query in an attempt to get total number(sum) of slides retrieving the max number from each project, however I am receiving the following error (#1111 — Invalid use of group function). Here’s the query:
SELECT COALESCE(project,'Total') as Project, SUM(MAX(slides)) as Slides
FROM projects_tbl
WHERE date BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY Project with ROLLUP
If I remove the SUM(), then the it works, however, I do not get an accurate total for all of the projects/slides.
Thanks in advance for any and all replies.
OMG Ponies
324k80 gold badges520 silver badges499 bronze badges
asked Jan 18, 2011 at 17:21
1
SELECT COALESCE(project,'Total') as Project, SUM(maxslides) AS slides
FROM (
SELECT project, MAX(slides) as maxslides
FROM projects_tbl
WHERE date BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY
project
) q
GROUP BY
project WITH ROLLUP
answered Jan 18, 2011 at 17:32
QuassnoiQuassnoi
411k91 gold badges613 silver badges613 bronze badges
0
You can try with something like:
SELECT sum(prjmax)
FROM
(SELECT COALESCE(project,'Total') as Project, MAX(slides) as prjmax
FROM projects_tbl
WHERE date BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY Project with ROLLUP)
You need to obtain the max for each project, and after this you can sum all.
answered Jan 18, 2011 at 17:30
BorjaBorja
2,1681 gold badge18 silver badges21 bronze badges
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)