MySQL - 1111 - Invalid use of group function-CodePudding

I'm getting an error of group function while trying to get the volatage stability per hour. table as an image below. table-image

SELECT  ip, 
        SUM(CASE HOUR(time) WHEN '1' THEN CAST(AVG(IF(volt=0,0,1)) AS DECIMAL(2,1)) ELSE 0 END) AS '1',
        SUM(CASE HOUR(time) WHEN '2' THEN CAST(AVG(IF(volt=0,0,1)) AS DECIMAL(2,1)) ELSE 0 END) AS '2',
        SUM(CASE HOUR(time) WHEN '3' THEN CAST(AVG(IF(volt=0,0,1)) AS DECIMAL(2,1)) ELSE 0 END) AS '3'
FROM UPS_Status
WHERE time BETWEEN NOW() - INTERVAL 24 hour AND NOW()
GROUP BY ip, HOUR(time)

CodePudding user response：

HOUR(time) does not belong in the GROUP BY clause. Try removing it:

SELECT 
    ip, 
    SUM(CASE HOUR(time) WHEN 1 THEN CAST(IF(volt=0, 0, 1) AS DECIMAL(2,1)) ELSE 0 END) AS `1`,
    SUM(CASE HOUR(time) WHEN 2 THEN CAST(IF(volt=0, 0, 1) AS DECIMAL(2,1)) ELSE 0 END) AS `2`,
    SUM(CASE HOUR(time) WHEN 3 THEN CAST(IF(volt=0, 0, 1) AS DECIMAL(2,1)) ELSE 0 END) AS `3`
FROM UPS_Status
WHERE 
    time BETWEEN NOW() - INTERVAL 24 hour AND NOW()
GROUP BY ip;