Q:

MySql Update Join two tables SET CASE invalid group function

Trying to update a table by joining two tables and using a CASE statement. 
I get the old Error Code: 1111 Invalid use of group function.   Any ideas on how to resolve? 
UPDATE table1 JOIN table2 ON (table1.ITEM = table2.ITEM)
SET table1.TURNOVERSCORE=CASE
WHEN ((COUNT(table2.ORDER_NUMBER))/12) < 1 AND table1.CAT = 'E' THEN .05
WHEN ((COUNT(table2.ORDER_NUMBER))/12) >= 1 AND ((COUNT(table2.ORDER_NUMBER))/12) < 4 AND table1.CAT = 'E' THEN .5
WHEN ((COUNT(table2.ORDER_NUMBER))/12) >= 4 AND table1.CAT = 'E' THEN 1
WHEN ((COUNT(table2.ORDER_NUMBER))/12) <= 5 AND table1.CAT ! = 'E' THEN .05
WHEN ((COUNT(table2.ORDER_NUMBER))/12) >= 6 AND ((COUNT(table2.ORDER_NUMBER))/12) < 11 AND table1.CAT ! = 'E' THEN .25
WHEN ((COUNT(table2.ORDER_NUMBER))/12) >= 11 AND ((COUNT(table2.ORDER_NUMBER))/12) < 21 AND table1.CAT ! = 'E' THEN .5
WHEN ((COUNT(table2.ORDER_NUMBER))/12) >= 21 AND table1.CAT ! = 'E' THEN 1 END

A:

I'm guessing that you are creating a cross join product, which I don't think is allowed when doing an update. 
Maybe try aggregating table 2 first, something like this:
UPDATE table1 
JOIN (SELECT count(*) as cnt, item FROM table2 GROUP BY item) t2 
ON table1.item = t2.item

SET table1.TURNOVERSCORE=CASE
WHEN t2.cnt/12 < 1 AND table1.CAT = 'E' THEN .05
WHEN t2.cnt/12 >= 1 AND t2.cnt/12 < 4 AND table1.CAT = 'E' THEN .5
WHEN (t2.cnt/12) >= 4 AND table1.CAT = 'E' THEN 1
WHEN (t2.cnt/12) <= 5 AND table1.CAT ! = 'E' THEN .05
WHEN (t2.cnt/12) >= 6 AND (t2.cnt/12) < 11 AND table1.CAT ! = 'E' THEN .25
WHEN (t2.cnt/12) >= 11 AND (t2.cnt/12) < 21 AND table1.CAT ! = 'E' THEN .5
WHEN (t2.cnt/12) >= 21 AND table1.CAT ! = 'E' THEN 1 END

