CODEWITHSUNDEEP
mysqlsql
cdalto
cdalto

Reputation: 857

MySQL Invalid Use of Group Function

I'm getting Error Code: 1111. Invalid use of group function when trying to build a query in MySQL. Apparently MySQL doesn't support WITH, which is what I'm more comfortable using.

SELECT DISTINCT `UserID`
FROM `user`
INNER JOIN `message`
    ON `user`.`Message` = `message`.`Recipient`
WHERE MAX(`TotalSize`) IN (
                SELECT SUM(`message`.`Size`) as `TotalSize`
                FROM `message`
                INNER JOIN `category`
                    ON `message`.`CatID` = `category`.`CatID`
                WHERE `category`.`CatName` = 'Inbox'
                GROUP BY `Recipient`);

Upvotes: 0

Views: 675

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You need to use HAVING clause instead of WHERE MAX(TotalSize)

SELECT DISTINCT `UserID`
FROM `user`
INNER JOIN `message`
    ON `user`.`Message` = `message`.`Recipient`
GROUP BY `UserID`
HAVING MAX(`message`.`Size`) IN (
                SELECT SUM(`message`.`Size`) as `TotalSize`
                FROM `message`
                INNER JOIN `category`
                    ON `message`.`CatID` = `category`.`CatID`
                WHERE `category`.`CatName` = 'Inbox'
                GROUP BY `Recipient`);

Group functions are not accessible in WHERE clause , HAVING can filter on aggregates.

Upvotes: 1

Barmar
Barmar

Reputation: 782508

SELECT `UserID`, MAX(SUM(message.Size)) as TotalSize
FROM `user`
INNER JOIN `message`
    ON `user`.`Message` = `message`.`Recipient`
INNER JOIN category
    ON message.CatID = category.CatID
WHERE category.CatName = 'Inbox'
GROUP BY UserID

Upvotes: 2

Related Questions