MYSQL Case Statements

Posted on 2011-05-02
Last Modified: 2012-05-11
Hi Experts

Is it possible to define variable within a case statement?


If I had three case statements that worked out different calculations. Could I give each case statement a variable which is equal to the value found, which I could call globally to do other calculations.

Case statement 1

WHEN theme_keywords.CompetingPages   < 50000                                                                                                                                                                    THEN  theme_keywords.CompetingPages/4000
WHEN theme_keywords.CompetingPages   > 50001                   AND  theme_keywords.CompetingPages   <= 1100000       THEN  theme_keywords.CompetingPages/15000
WHEN theme_keywords.CompetingPages   > 1100001             AND  theme_keywords.CompetingPages   <= 10000000       THEN  theme_keywords.CompetingPages/12000
WHEN theme_keywords.CompetingPages   > 10000001       AND  theme_keywords.CompetingPages   <= 20000000       THEN  theme_keywords.CompetingPages/9000
WHEN theme_keywords.CompetingPages   > 20000001       AND  theme_keywords.CompetingPages   <= 30000000       THEN  theme_keywords.CompetingPages/8500
WHEN theme_keywords.CompetingPages   > 30000001       AND  theme_keywords.CompetingPages   <= 40000000       THEN  theme_keywords.CompetingPages/8000
WHEN theme_keywords.CompetingPages   > 40000001       AND  theme_keywords.CompetingPages   <= 213000000 THEN  theme_keywords.CompetingPages/7800      
WHEN theme_keywords.CompetingPages   > 213000000                                                                                                                                                            THEN  theme_keywords.CompetingPages/7500      

If I had case statement 2


case statement 3

and wanted to take

case statement 1 + case statement 2 + case statement 3 and add them all together to get a value as an example how would I do this?

Is this possible?

Question by:matthewdacruz
    LVL 142

    Accepted Solution

    you have 2 options:
    * copy the 3 case statements for that expression ( case  ... end + case ... end + case .. end )
    * use inline view:
    select res1 , res2, res3 , res1 + res2 + res3 from (select case .. end res1, case .. end res2 , case ... end res3 from ... where .. ) 

    Open in new window

    note that you might consider to create a lookup table with the ranges and the / value ... to simplify the code, and make it more flexible (aka configurable)

    Author Comment

    Is if possible to use the @variable in the case staement?

    Author Comment

    How would you create a lookup table to work with this?

    Author Closing Comment

    Thanks your suggestion worked well

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now