CODEWITHSUNDEEP
mysqlcountsumcase
Rob
Rob

Reputation: 3

SQL CASE with COUNT or SUM issue

I have tried some options but it simply does not work or does not fit my needs.

Its about the following SQL (This does not work but is most close to what i need):

SELECT CASE WHEN r.`content` LIKE '% word1 %' 
            then 1 
            else 0 
      END AS `val1`
    , CASE WHEN r.`content` LIKE '% word2 %' 
           then 1 
           else 0 
      END AS `val2`
    , CASE WHEN r.`city` LIKE 'cityname' 
           then 1 
           else 0 
      END AS `val3`
    , (`val1`+`val2`+`val3`) AS `reference_value` 
FROM `testimonials` as r 
WHERE `accepted`='1' 
ORDER BY `reference_value`

So basically the entry with the most points end on top. When the 3 cases match reference_value will have 3 points. The code is dynamically so it can also be 4,5,6,7 or more CASEs.

I hope this makes any sense to you. Thanks in advance.

Upvotes: 0

Views: 119

Answers (2)

Pred
Pred

Reputation: 9042

You can't reuse calculated values (like val1 in your query) in the same SELECT list.

Use a subquery, or repeat the CASEs to sum their values. My preference is subquery in this case, but the actual requirements and the actual execution plan (explain) can overwrite it at any time.

SELECT
  val1,val2,val3,
  val1+val2+val3 as reference_value
FROM (
  SELECT CASE WHEN r.`content` LIKE '% word1 %' 
            then 1 
            else 0 
      END AS `val1`
    , CASE WHEN r.`content` LIKE '% word2 %' 
           then 1 
           else 0 
      END AS `val2`
    , CASE WHEN r.`city` LIKE 'cityname' 
           then 1 
           else 0 
      END AS `val3`
  FROM `testimonials` as r 
  WHERE `accepted`='1' 
) TMP
ORDER BY `reference_value`

Upvotes: 3

mallik1055
mallik1055

Reputation: 99

SELECT *,val1+val2+val3 AS `reference_value` FROM (
    CASE WHEN r.content LIKE '% word1 %' 
        then 1 
        else 0 
    END AS `val1`
   ,CASE WHEN r.content LIKE '% word2 %' 
       then 1 
       else 0 
   END AS `val2`
  ,CASE WHEN r.city LIKE 'cityname' 
       then 1 
       else 0 
  END AS `val3`
  ,  
  FROM `testimonials` as r 
  WHERE `accepted`='1' 
)
ORDER BY `reference_value`

Upvotes: 0

Related Questions