Matkey Matkey - 4 months ago 12
MySQL Question

error: 1111 Invalid use of group function

I have searched for solutions but all seem to need to replace WHERE with HAVING. I don't believe this to have the same issue and I am unable to figure out what could be wrong. I have tried replacing different parts of the code where I had references and any aggregate functions in the WHERE clause but I am still left with the same error. The following is my entire MySQL statement which fetches statistics for sellers and their performance for a chosen period.

SELECT users.name AS sellername, users.id AS sellerid, `providers`.`id`, providers.name AS providername,
COUNT(sales.id) as brutto, COUNT(sale_netto.id) as netto, AVG(sale_quality.grade) as grade,
SUM(COUNT(sales.id)-COUNT(sale_netto.id)) as diff, (COUNT(sales.id)/COUNT(sales.id)+COUNT(sale_netto.id))*100 AS perc
FROM `product_regretrules`
LEFT JOIN `product_categories` ON `product_categories`.`id` = `product_regretrules`.`product_category_id`
LEFT JOIN `product_product_category` ON `product_product_category`.`product_category_id` = `product_categories`.`id`
LEFT JOIN `products` ON `products`.`id` = `product_regretrules`.`product_id`
OR `products`.`id` = `product_product_category.product_id`
LEFT JOIN `sales` ON `sales`.`product_id` = `products`.`id`
LEFT JOIN `sale_netto` ON `sale_netto`.`sale_id` = `sales`.`id`
LEFT JOIN `users` ON `users`.`id` = `sales`.`seller_id`
LEFT JOIN `providers` ON `providers`.`id` = `products`.`provider_id`
LEFT JOIN `sale_quality` ON `sale_quality`.`sale_id` = `sales`.`id`
WHERE `sale_netto`.`upload_id` = 725
AND `sale_netto`.`id` is not null
AND `sales`.`sell_date` between 2015-12-04 and 2016-08-06
AND `product_regretrules`.`deleted_at` is null
GROUP BY `users.id`, `providers`.`id`


Please look at the problem with the query, I am not interested in answers or comments on how to get the right numbers/stats.

Answer

Try using this for the GROUP BY:

GROUP BY  users.name, users.id, `providers`.`id`, providers.name

You may have strict GROUP BY mode on. This causes MySQL to follow the ANSI standard and not allow "bare" columns in the SELECT in an aggregation query.

Alternatively, you can change the SELECT to:

SELECT MAX(users.name) AS sellername, users.id AS sellerid,
      `providers`.`id`, MAX(providers.name) AS providername,

EDIT:

Oh, on closer inspection, I see the problem. You have:

SUM(COUNT(sales.id)-COUNT(sale_netto.id)) as diff

You can't nest aggregation functions. Presumably, you intend:

(COUNT(sales.id)-COUNT(sale_netto.id)) as diff