MySQL Case/If/Then

case, mysql, sql

Solution

I thin you mean that if the `stock > 1000` then another color `not green`.

SELECT  Beer.Beer, 
        Beer.Brewery, 
        Beer.Style, 
        Beer.ABV, 
        Beer.Hops, 
        Beer.SRM,
        Sum(BeerStock.Quantity) totalQuantity,
        CASE WHEN Sum(BeerStock.Quantity) < 0 THEN ''
            WHEN Sum(BeerStock.Quantity) BETWEEN 0 AND 999 THEN 'Red'
            WHEN Sum(BeerStock.Quantity) BETWEEN 1000 AND 4999 THEN 'Yellow'
            WHEN Sum(BeerStock.Quantity) BETWEEN 5000 AND 9999 THEN 'GREEN'
            WHEN Sum(BeerStock.Quantity) >= 10000 THEN 'Another Color' 
        END
FROM    Beer
        INNER JOIN BeerStock
            ON Beer.Beer = BeerStock.Beer
-- Where    ..other conditions..

but if you really mean that,

SELECT  Beer.Beer, 
        Beer.Brewery, 
        Beer.Style, 
        Beer.ABV, 
        Beer.Hops, 
        Beer.SRM,
        Sum(BeerStock.Quantity) totalQuantity,
        CASE WHEN Sum(BeerStock.Quantity) < 0 THEN ''
            WHEN Sum(BeerStock.Quantity) BETWEEN 0 AND 999 THEN 'Red'
            WHEN Sum(BeerStock.Quantity) BETWEEN 1000 AND 4999 THEN 'Yellow'
            WHEN Sum(BeerStock.Quantity) >= 5000 THEN 'GREEN'
        END
FROM    Beer
        INNER JOIN BeerStock
            ON Beer.Beer = BeerStock.Beer
-- Where    ..other conditions..

One more thing, You also need to use `GROUP BY` clause or else you will be returning only one record even if you have different records,

SELECT  Beer.Beer, 
        Beer.Brewery, 
        Beer.Style, 
        Beer.ABV, 
        Beer.Hops, 
        Beer.SRM,
        Sum(BeerStock.Quantity) totalQuantity,
        CASE WHEN Sum(BeerStock.Quantity) < 0 THEN ''
            WHEN Sum(BeerStock.Quantity) BETWEEN 0 AND 999 THEN 'Red'
            WHEN Sum(BeerStock.Quantity) BETWEEN 1000 AND 4999 THEN 'Yellow'
            WHEN Sum(BeerStock.Quantity) BETWEEN 5000 AND 9999 THEN 'GREEN'
            WHEN Sum(BeerStock.Quantity) >= 10000 THEN 'Another Color' 
        END
FROM    Beer
        INNER JOIN BeerStock
            ON Beer.Beer = BeerStock.Beer
-- Where    ..other conditions..
GROUP   BY Beer.Beer, 
        Beer.Brewery, 
        Beer.Style, 
        Beer.ABV, 
        Beer.Hops, 
        Beer.SRM

Problem

I am trying to build a query in MySQL, where I have a numeric stock level of something and depending on what that stock level is, I want it to return another value that would be a colour code. E.g. If the stock level is <0 then EMPTY if stock level is Between 0 and 1000 then RED, If stock level is Between 1000 and 5000 then YELLOW, If Stock level is Between 5000 and 10000 then GREEN, If Stock level is >10000 then GREEN. So here is my example. I have a Beer table that holds info on the Beer and a BeerStock table that holds the Stock Values. ``` SELECT Beer.Beer, Beer.Brewery, Beer.Style, Beer.ABV, Beer.Hops, Beer.SRM, Sum(BeerStock.Quantity) FROM Beer, BeerStock Where Beer.Beer = BeerStock.Beer ``` That would give me something like this: ``` Beer1 Brewery1 Style1 5%, 3, 10, 1238 ``` And I want ``` Beer1 Brewery1 Style1 5%, 3, 10, 1238 YELLOW ``` I can't seem to figure this out at all, should I be using a CASE or can I use an IF/THEN type of thing? My SQL skills are rusty, normally I use Access and mess with the auto-generated SQL rather than writing from scratch...I can cope with the basics of MySQL but not this kind of stuff! Any advice/pointers appreciated.... Thanks Andy

Original source