How to see IF field1==1 and some other questions.

[yavor9]

Hi,
I need some help for a query, and I really have no idea how to write such a select.
table looks like:

| id | username | votes | vip |

(simplified of course)
Here is what i need

1. select all fields from table `users`
2. while selecting check if the column `vip` is 1 or 0
3. if `vip`= 0, then multiply column `votes` by 1.1 (vip users get 110% votes)
4. ORDER BY `votes` DESC

can i do this with a select query? how?

thanks in advance!

[r937]

SELECT id
     , username
     , votes
     , vip 
     , CASE WHEN vip = 0
            THEN 1.1
            ELSE 1.0 END * votes AS calc_votes
  FROM users
ORDER
    BY calc_votes DESC

[yavor9]

just did it myself, took a little more deep and profound brain things inside my head

SELECT
(CASE `vip` WHEN '1' THEN `votes_total`* 1.1 ELSE `votes_total` END) as `votes_total`,
 `vip` FROM `users`  ORDER BY `votes_total` DESC;

thanks again

[r937]

make sure you test that, because i think it has an error

you cannot assign votes_total as the value in the THEN clause, if votes_total is the column alias of the very same CASE expression

also, if the vip and the vote calculation are the only columns in your SELECT clause, then the query will run but the results will be pretty useless -- you'll probably want the username in the SELECT clause as well

[yavor9]

You are right - pretty useless, but it was just the test query - it worked well,
needed to add `username` and other columns to SELECT clause, also ROUND(`votes_total`* 1.1). There was no error in the example in my previous post, it was just too simple.

All tested and put in production

SELECT
 (CASE `vip` WHEN '1' 
 THEN ROUND(`votes_total`* 1.1) 
 ELSE `votes_total` END) 
	 as `votes_total`,
	 `vip`,
	 `username`,
	 ...		 		
	  FROM `users` 
	  WHERE `banned`=0 
	  AND `active`=1 
	  ORDER BY `votes_total` DESC 
	  LIMIT 50;