logo
down
shadow

Why am I getting 'invalid use of group function' error in mysql update?


Why am I getting 'invalid use of group function' error in mysql update?

By : user2948298
Date : November 14 2020, 04:48 PM
I wish this help you You can't just use max() like that in set. Adding ORDER BY Number DESC LIMIT 1 to the m subquery might do what you want:
code :
UPDATE `Scores` s INNER JOIN
       `Players` p
       ON s.PlayerID = p.PlayerID INNER JOIN
       (SELECT PlayerID, Number
        FROM `NumberHistory` h
        WHERE PlayerID = 136
        ORDER BY Created DESC
        LIMIT 1
       ) f
       ON p.PlayerID = f.PlayerID INNER JOIN
       (SELECT Number FROM `Players` WHERE PlayerID = 136
        UNION ALL
        SELECT Number FROM `NumberHistory` WHERE PlayerID = 136
        ORDER BY Number DESC
        LIMIT 1
       ) m LEFT OUTER JOIN
       (SELECT PlayerID, COUNT(PlayerID) as Activity
        FROM `NumberHistory`
        WHERE Created BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW()
       ) a
       ON p.PlayerID = a.PlayerID
 SET s.PlayerID = p.PlayerID,
     s.PlayerName = p.PlayerName, 
     s.Number = m.Number,
     s.PreviousNumber = f.Number,
     s.Diff = (p.Number-IFNULL(f.Number,0)),
     s.Activity = IFNULL(a.Activity, 0)
WHERE s.PlayerID = 136


Share : facebook icon twitter icon
mySQL query error 'Invalid use of group function'

mySQL query error 'Invalid use of group function'


By : user3159285
Date : March 29 2020, 07:55 AM
will help you The problem was with the MAX() part of the sub query. I removed it and replaced it with the numbers and it worked a treat.
mysql error - Invalid use of group function

mysql error - Invalid use of group function


By : Ethan Simpson
Date : March 29 2020, 07:55 AM
this will help The following query returns results which contains count greater than 0 i.e, it ignores the records which has no entry in om.memberid , You could use LEFT JOIN.
code :
SELECT o.id, o.organizationname, o.organizationwebsite, o.renewDate, COUNT( om.memberid ) 
FROM organization o
LEFT JOIN organizationmember om ON om.organizationid = o.id 
GROUP BY o.id
MySql Update Join two tables SET CASE invalid group function

MySql Update Join two tables SET CASE invalid group function


By : kroman
Date : March 29 2020, 07:55 AM
will be helpful for those in need 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:
code :
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
mysql | Getting Database error, Invalid Use of Group Function

mysql | Getting Database error, Invalid Use of Group Function


By : Trenton Liebman
Date : March 29 2020, 07:55 AM
Does that help The problem is that you have aggregation functions in the where clause:
code :
SELECT `f`.`FormID`, `f`.`FormName`, `f`.`FormCIPath`, MIN(g.IsMenuLink) AS IsMenuLink, 
       GROUP_CONCAT(DISTINCT g.GroupID ORDER BY g.GroupID) AS GroupIDs
FROM (`sys_forms` f) INNER JOIN
     `sys_forms_in_groups` g
     ON `g`.`FormID` = `f`.`FormID`
WHERE `g`.`GroupID` IN ('1', '1') AND
       (UPPER(f.FormName) LIKE '%DA%' OR
        UPPER(f.FormCIPath) LIKE '%DA%' OR
        UPPER(MIN(g.IsMenuLink)) LIKE '%DA%'
--------------^
       )
GROUP BY `f`.`formID`
LIMIT 25;
WHERE `g`.`GroupID` IN ('1', '1') AND
       (UPPER(f.FormName) LIKE '%DA%' OR
        UPPER(f.FormCIPath) LIKE '%DA%' OR
        UPPER(g.IsMenuLink) LIKE '%DA%'
       )
SELECT `f`.`FormID`, `f`.`FormName`, `f`.`FormCIPath`, MIN(g.IsMenuLink) AS IsMenuLink, 
       GROUP_CONCAT(DISTINCT g.GroupID ORDER BY g.GroupID) AS GroupIDs
FROM (`sys_forms` f) INNER JOIN
     `sys_forms_in_groups` g
     ON `g`.`FormID` = `f`.`FormID`
WHERE `g`.`GroupID` IN ('1', '1') AND
       (UPPER(f.FormName) LIKE '%DA%' OR
        UPPER(f.FormCIPath) LIKE '%DA%'
       )
GROUP BY `f`.`formID`
HAVING UPPER(MIN(g.IsMenuLink)) LIKE '%DA%'
LIMIT 25;
MySQL: update with join using GROUP_CONCAT: ERROR 1111 (HY000): Invalid use of group function

MySQL: update with join using GROUP_CONCAT: ERROR 1111 (HY000): Invalid use of group function


By : kphy
Date : March 29 2020, 07:55 AM
around this issue so I am updating a table that's functionally equivalent to: , Try something like this:
code :
update NODE n1 
join 
(
    SELECT
        GROUP_CONCAT(full_name SEPARATOR '&') as name_concat, 
        group_name
    FROM NODE
    WHERE
       is_top='0'
    GROUP BY group_name
) n2 
ON n2.group_name=n1.group_name 
SET n1.dependency=n2.name_concat
WHERE 
    n1.is_top='1' and 
    n1.group_name='MY GROUP NAME';
Related Posts Related Posts :
  • How to handled the null values in WHERE IN condition in mysql
  • Is it okay to update primary key field to shift a record to the top?
  • Stop query through pdo
  • How to check whether particular value in field in MySQL exists
  • Execute a statement for every table in a database
  • insertion 74G data into mysql table cost more then 2 days, how to improve insert performance
  • Where can I see the differents "Hibernate Console Configurations" in Eclipse
  • MySql | relational database vs non relational database in terms of Performance
  • Exporting mysql database using mysqldump including procedures
  • Trigger MySQL not working
  • How do I update values for individual rows quickly?
  • MySQL join query with multiple where condition
  • MySQL stored procedure is not working
  • MySQL - Order By exact match at the end
  • Select max date or null date from same table MySQL
  • Best solution workaround for needing a mysql event on insert?
  • MySQL select several columns of several tables at the same time without using JOIN
  • Is it more efficient to have a table with one of its columns mostly empty or make a new table and refer to it?
  • mysql data truncated with ñ character
  • MySQL Distinct Active Users for the last month query
  • Get all rows that match another attribute in the same table
  • MySQL statement to get min value with max ID
  • DATEDIFF Current/Date for Last Record
  • What is the logic or procedure for combining multiple DB query's results into one elegant table?
  • Modify TIMESTAMP MySQL for use as Primary Key
  • Django - Efficiently bulk create inherited models
  • MySQL Dual table Join Dual table
  • Ruby on Rails - database where sorted by id
  • MySQL 5.6 : Access denied for user 'root' @ 'localhost' when password contains backslash
  • sql query case dateadd
  • Click on one button multiple times and mySql
  • Select daily amount of specific values from same column
  • How to send e-mail to users within a database?
  • Updating self joined table
  • Convert query to a Left-Join (?) to include userid's without a match
  • How to Convert from .Net TimeZone to MySQL TimeZone
  • Syntax Error for MySQL Trigger
  • SQL Optimization - 3 Tables - Multiple SUMs - 20k records - 12 Seconds
  • MySQL: Why does query give NULL as a result of SUM?
  • Simple query issue with multiple tables and mismatching IDs
  • MySQL join multiple lines
  • Performing JOIN between two tables
  • Get specific rows from group by clause in PostgreSQL
  • MySQL query to get the sum of a column
  • Why mongoDB takes less time for Select than Fetch time?
  • Laravel 4.2 BIT datatype issue
  • MySQL Join Query confuse
  • Select distinct rows by count in descending order
  • Select query with comma and join
  • Adding a percentage (%) sign to each value while using select sql statement
  • How to access data from foreign key table
  • How to use the SQL MINUS keyword
  • Mysql Left Join statement ignored during query
  • Mysql version is different in phpmyadmin
  • MySQL: creating comma separated list and using in IN
  • calculating total time using timestamp
  • SQL LIKE REGEXP Matching Single Value and Comma'd String
  • one table or several table in mysql
  • Selecting rows with null in integer columns using mysql query
  • Using MySQL Database Triggers
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org