Greetings comrades, please help make the following request: Output all sellers who have sold more in a month than seller “Ivanov”. Data available only for one month. enter image description here

Tried to do something: it turned out like this and error 1111:

SELECT l_name FROM sales WHERE SUM(the_amount_of_the_sale) > ANY ( SELECT SUM(the_amount_of_the_sale) FROM sales WHERE l_name = 'Ivanov' ) AND l_name <> 'Ivanov';
  • ошибка 1111 - what is written in the text of the error? - Alexey Shimansky
  • ERROR 1111 (HY000): Invalid use of group function - Suspicious cat
  • the sum () function cannot be used in where, since this part of the query is done before grouping. You can use the phrase having and any not needed here, because the subquery returns only one result. In the main query, of course, there should be a group by - Mike
  • What does "a month" mean? that is, from 1 to the current or the last 30 days or what? Does it really matter now? - Alexey Shimansky
  • In the table, the data is only for one month, it does not carry a semantic load. You just need to find out from the whole table who sold more than Ivanov - Suspicious Cat

1 answer 1

 SELECT l_name FROM `sales` WHERE user_name <> "Ivanov" GROUP BY l_name HAVING SUM(the_amount_of_the_sale) > ( SELECT SUM(the_amount_of_the_sale) FROM `sales` WHERE l_name ="Ivanov" )

But this does not take into account that mythical month.

and, in principle, it works perfectly without WHERE user_name <> "Ivanov" , because we are now filtering off Ivanov in inequality in having