MySQL NFL QB rating - possible to set a minimum and maximum value if a sum adds up beyond a set range?

user_16667832 0
Viewed 29

I am trying to calculate the NFL's quarterback rating in MySQL. I have my four categories, but I need to be able to set a limit on each of them. If it adds up to a negative number, it needs to be 0. And if it adds up higher than 2.375 I need it to be 2.375.

How can I add a minimum and maximum value to each of these four queries?

ROUND(((sum(case when pass_result='C' then 1 else 0 end)/sum(case when run_pass='P' then 1 else 0 end))-0.3)*5,3) AS 'Rule 1',
ROUND(((sum(gain)/sum(case when run_pass='P' then 1 else 0 end))-3)*0.25,3) AS 'Rule 2',
ROUND((sum(case when series_end='Touchdown' then 1 else 0 end)/sum(case when run_pass='P' then 1 else 0 end))*20,3) as 'Rule 3',
ROUND((2.375-(sum(case when pass_result='IN' then 1 else 0 end)/sum(case when run_pass='P' then 1 else 0 end))*25),3) as 'Rule 4'
statistics mysql
1 Answers

You can use the functions GREATEST() and LEAST():

LEAST(GREATEST(ROUND(((SUM(pass_result='C') / SUM(run_pass='P'))-0.3)*5,3), 0), 2375) AS Rule1,
LEAST(GREATEST(ROUND(((SUM(gain)/SUM(run_pass='P'))-3)*0.25,3), 0), 2375) AS Rule2,
LEAST(GREATEST(ROUND((SUM(series_end='Touchdown') / SUM(run_pass='P'))*20,3), 0), 2375) AS Rule3,
LEAST(GREATEST(ROUND((2.375-(SUM(pass_result='IN')/SUM(run_pass='P'))*25),3), 0), 2375) AS Rule4

When you apply GREATEST() to a x value like:

GREATEST(x, 0)

then the if x is negative the result is 0.

When you apply LEAST() to a x value like:

LEAST(x, 2375)

then the if x is greater than 2375 the result is 2375.

I also simplified the code by using only the boolean expressions inside SUM() instead of the CASE expressions, because boolean expressions are evaluated as 1 for true and 0 for false.

user_104988280
Related