I've a table User_Engagement having columns :-

enter image description here

I was trying to implement this below formula on my update Query to calculate TotalInteraction of all columns in a single query:-

totalInteraction =   value in totalComments + value in totalLikes * ( SUM of averageLikes / SUM of averageComments)

So far I've tried to implement this query :-

UPDATE User_engagement AS ue 
ue.totalInteraction = (ue.totalComments + ue.totalLikes) * (SUM(ue.averageLikes)/SUM(ue.averageComments))

This above query is giving me an error #1111 - Invalid use of group function in MySql.

A help is highly appreciated.


One approach:

SELECT @ratio := SUM(averageLikes)/SUM(averageComments)
    FROM User_engagement;
UPDATE User_engagement
    SET totalInteraction = (totalComments + totalLikes) * @ratio;

But that may be impractical... What happens when anything changes in the table? Won't you have to recompute and update all the rows in the table?

Instead, do the computation when you need it:

SELECT ue.totalLikes, ue.averageLikes, ue.averageComments,
       (ue.totalComments + ue.totalLikes) * x.ratio AS totalInteraction
    FROM User_engagement AS ue
    JOIN ( SELECT SUM(averageLikes)/SUM(averageComments) as ratio
            FROM User_engagement ) AS x

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.