Search code examples
mysqlcase-statement

MYSQL Case statement addition

I have a rather simple query but want to order the results based on a score column that is generated by certain cases, Im not even sure if you can do this sort of thing in MYSQL.

But the basic idea is, the more columns that are not set to 'Any' the higher score will be and it will be at the top of the list.

A data example would be:

Giant, Bikes, Road bike, Any,Any, would have a higher score than Any,Bikes, Road bikes,Any,Any

this is ideally how I would like it to work (if even possible):

SELECT Brand, Cat1, Cat2, Cat3, Gender,
CASE 
  WHEN Brand ='Any' THEN score = 0 ELSE score = 1 
  WHEN Cat1 !='Any' THEN score +1 ELSE score = score (stays the same)
  WHEN Cat2 !='Any' THEN score +1 ELSE score = score (stays the same)
and so on....

END as weight

FROM products_sizeguides 
ORDER BY weight DESC

Thanks

Solution

  • Try adding together rather than trying to add as you go:

    SELECT Brand, Cat1, Cat2, Cat3, Gender,
IF(Brand = 'Any',0,1)
+ IF(Cat1 !='Any,1,0)
+ IF(Cat2 !='Any,1,0)
as weight
FROM products_sizeguides 
ORDER BY weight DESC