Create a ranking system in SQL, Variables problems

Question

I've some trouble with my query to create a ranking system. My query is good, but I use some variable, and the results are inconsistent.

The first case statement is good, but not the second. Did I have a possibility to make this second calcul without use variable ?

        CASE
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 0 AND 100 AND DATEDIFF(NOW(),MAX(pso.date_add))        BETWEEN 0 AND 50        THEN @scoring := ROUND(50 + SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 0 AND 100 AND DATEDIFF(NOW(),MAX(pso.date_add))        BETWEEN 51 AND 150      THEN @scoring := ROUND(20 + SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 0 AND 100 AND DATEDIFF(NOW(),MAX(pso.date_add))        BETWEEN 151 AND 364     THEN @scoring := ROUND(SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 0 AND 100 AND DATEDIFF(NOW(),MAX(pso.date_add))        >= 365                  THEN @scoring := ROUND(SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/20 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 101 AND 499 AND DATEDIFF(NOW(),MAX(pso.date_add))      BETWEEN 0 AND 50        THEN @scoring := ROUND(50 + SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/3 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 101 AND 499 AND DATEDIFF(NOW(),MAX(pso.date_add))      BETWEEN 51 AND 150      THEN @scoring := ROUND(20 + SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/3 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 101 AND 499 AND DATEDIFF(NOW(),MAX(pso.date_add))      BETWEEN 151 AND 364     THEN @scoring := ROUND(SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/3 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) BETWEEN 101 AND 499 AND DATEDIFF(NOW(),MAX(pso.date_add))      >= 365                  THEN @scoring := ROUND(SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/20 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) >= 500 AND DATEDIFF(NOW(),MAX(pso.date_add))                   BETWEEN 0 AND 50        THEN @scoring := ROUND(50 + SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/5 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) >= 500 AND DATEDIFF(NOW(),MAX(pso.date_add))                   BETWEEN 51 AND 150      THEN @scoring := ROUND(20 + SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/5 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) >= 500 AND DATEDIFF(NOW(),MAX(pso.date_add))                   BETWEEN 151 AND 364     THEN @scoring := ROUND(SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/5 + COUNT(pso.id_order)*10,0)
            WHEN SUM(pso.total_products_wt)/COUNT(pso.total_products_wt) >= 500 AND DATEDIFF(NOW(),MAX(pso.date_add))                   >= 365                  THEN @scoring := ROUND(SUM(pso.total_products_wt)/COUNT(pso.total_products_wt)/20 + COUNT(pso.id_order)*10,0)
        END AS 'Scoring',
        CASE
            WHEN @scoring >= 200 THEN '5/5'
            WHEN @scoring BETWEEN 150 AND 199 THEN '4/5'
            WHEN @scoring BETWEEN 100 AND 149 THEN '3/5'
            WHEN @scoring BETWEEN 50 AND 99 THEN '2/5'
            WHEN @scoring <= 49 THEN '1/5'
        END AS 'Rank'

Answer 1

From the docs https://dev.mysql.com/doc/refman/5.5/en/user-variables.html:

However, the order of evaluation for expressions involving user variables is undefined.

So in your second expression you may read the variable @scoring right after it's been modified by the first expression (as you expect), or just right before that.

Solution:

SELECT 
  "Scoring",
  CASE
      WHEN "Scoring" >= 200 THEN '5/5'
      WHEN "Scoring" BETWEEN 150 AND 199 THEN '4/5'
      WHEN "Scoring" BETWEEN 100 AND 149 THEN '3/5'
      WHEN "Scoring" BETWEEN 50 AND 99 THEN '2/5'
      WHEN "Scoring" <= 49 THEN '1/5'
  END AS "Rank"
FROM
(
  SELECT 
    ...
    CASE WHEN ... END AS "Scoring" 
    ...
  FROM pso
  ...
) subquery