CODEWITHSUNDEEP
mysqlsqlif-statement
Stanislav
Stanislav

Reputation: 913

Using IF statement in MySQL and further calculation useing the result of the statement

I have an issue. I am trying to use IF statement in my SQL query and them use the result of this statement in further calculations:

SELECT average_rating, number_of_ratings,    
    IF(number_of_ratings < 500 AND number_of_ratings > 100, 0.90, 
        IF(number_of_ratings>=500 AND number_of_ratings<=1000, 0.95, 
            IF(number_of_ratings>1000, 0.99, 0.80)
        )
    ) AS rating_factor,    
    ROUND((rating_factor * average_rating), 4) AS factored_rating    
FROM table
ORDER by factored_rating DESC

However, it doesn't work and returns an error: Unknown column 'rating_factor' in 'field list'

Does anybody has an idea how to make it work?

Thanks in advance.

Upvotes: 0

Views: 2344

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

Alain's query will work if you don't need BOTH the rating_factor and the factored_rating columns. njk's query is an ANSI compliant version of subquerying.

However, since you're using MySQL, you can store values using temporary variables. I have also simplified your IF conditions.

SELECT
    average_rating,
    number_of_ratings,
    @x := IF(number_of_ratings> 1000, 0.99,
          IF(number_of_ratings>=500 , 0.95,
          IF(number_of_ratings> 100 , 0.90,
                                      0.80))) rating_factor,
    ROUND((@x * average_rating), 4) AS factored_rating
FROM mytable
ORDER by factored_rating DESC

Here's an SQLFiddle to show this query.
For posterity, the sample is replicated below.

drop table if exists mytable;
create table mytable (
average_rating int,
number_of_ratings int,
factored_rating int);
insert into mytable
select 5,2,3 union all
select 4,1,5 union all
select 12,3,1 union all
select 11,4,2 union all
select 8,2,12;

Result of the query

"average_rating";"number_of_ratings";"rating_factor";"factored_rating"
"12";"3";"0.80";"9.6000"
"11";"4";"0.80";"8.8000"
"8";"2";"0.80";"6.4000"
"5";"2";"0.80";"4.0000"
"4";"1";"0.80";"3.2000"

Upvotes: 3

Alain Collins
Alain Collins

Reputation: 16362

Use the result of the IF statement in your formula. I think I got the parens right, but you should see the idea:

SELECT average_rating, number_of_ratings,    
  ROUND((
    IF(number_of_ratings < 500 AND number_of_ratings > 100, 0.90, 
        IF(number_of_ratings>=500 AND number_of_ratings<=1000, 0.95, 
            IF(number_of_ratings>1000, 0.99, 0.80)
        )
    ) * average_rating), 4) as factored_rating    
FROM table
ORDER by factored_rating DESC

Upvotes: 1

Kermit
Kermit

Reputation: 34063

This should help:

SELECT a.average_rating, a.number_of_ratings, ROUND((a.rating_factor * a.average_rating), 4) AS factored_rating
FROM
    (SELECT average_rating, number_of_ratings,    
        IF(number_of_ratings < 500 AND number_of_ratings > 100, 0.90, 
            IF(number_of_ratings>=500 AND number_of_ratings<=1000, 0.95, 
                IF(number_of_ratings>1000, 0.99, 0.80)
            )
        ) AS rating_factor   
    FROM table
    ORDER by factored_rating DESC) a

Upvotes: 1

Related Questions