SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member yavor9's Avatar
    Join Date
    Dec 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member yavor9's Avatar
    Join Date
    Dec 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just did it myself, took a little more deep and profound brain things inside my head
    Code:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member yavor9's Avatar
    Join Date
    Dec 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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;
    Last edited by yavor9; Dec 9, 2009 at 12:06. Reason: adding some code


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •