Compare sum of two columns to one column-CodePudding

I am trying to compare the sum of two column values to a third column values, then display a string literal as result set value. Here's my query

Here's my schema with sample data

Player_Games
------------------------
Game1 | Game 2 | Game 3
------------------------
 20   |   13   |    45
------------------------
 14   |   27   |    25
------------------------
 18   |   17   |    36
------------------------
 20   |   20   |    29
------------------------
 32   |   10   |    33
------------------------

SELECT 
CASE
    WHEN((
        SELECT SUM(Game1   Game2) as total FROM Player_Games
        ) 
        < 
        (
        SELECT Game3 FROM Player_Games
        ))
         THEN "Expected_Performance"
END as Result

FROM Player_Games;

Expected Result

Expected Performance
NULL
Expected Performance
NULL
NULL

However, an error is thrown ERROR 1242 (21000) at line 4: Subquery returns more than 1 row

What am I missing here? Do I need to GROUP BY Game3?

CodePudding user response：

you dont need sum here. just case :

    SELECT 
    CASE
        WHEN Game1   Game2 < Game3
             THEN 'Expected_Performance'
    END as Result
FROM Player_Games

CodePudding user response：

You can just check using a CASE expression whether the sum of game1 and game2 is less than game3 then add the needed text.

Query

select 
case when game1   game2 < game3
   then 'Expected performance' 
   else null end as result
from player_games;

CodePudding user response：

SELECT CASE WHEN t1.Game3 > t2.total THEN "Expected_Performance" END
FROM Player_Games t1
CROSS JOIN (SELECT SUM(Game1   Game2) as total FROM Player_Games) t2

On 8

SELECT CASE WHEN Game3 > SUM(Game1   Game2) OVER () THEN "Expected_Performance" END
FROM Player_Games

If you need to summarize not over whole table but only in single row then

SELECT CASE WHEN Game3 > Game1   Game2 THEN "Expected_Performance" END
FROM Player_Games

PS. There is no guarantee that the output rows order matches the source one (in all queries). So you must add some column into the output list which identifies separate rows.