MySQL Case/If/Then

Question

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

Answer 1

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