Bartonb12 Bartonb12 - 23 days ago 6
MySQL Question

Use variable for case statement mysql

I have this query

select
count(so.shop_order_id),
case
when so.subtotal_amount < 99.99 then 'under 100'
when so.subtotal_amount between 100.00 and 149.99 then '100-149'
when so.subtotal_amount between 150.00 and 249.99 then '150-249'
when so.subtotal_amount between 250.00 and 399.99 then '250-399'
when so.subtotal_amount between 400.00 and 499.99 then '400-499'
when so.subtotal_amount > 500 then '500+'
end

from shop_order so

where so.created_at between '2015-11-29 23:59:59' and '2015-11-31 00:00:01'
and (case
when so.subtotal_amount < 99.99 then 'under 100'
when so.subtotal_amount between 100.00 and 149.99 then '100-149'
when so.subtotal_amount between 150.00 and 249.99 then '150-249'
when so.subtotal_amount between 250.00 and 399.99 then '250-399'
when so.subtotal_amount between 400.00 and 499.99 then '400-499'
when so.subtotal_amount > 500 then '500+'
end) is not null

group by (case
when so.subtotal_amount < 99.99 then 'under 100'
when so.subtotal_amount between 100.00 and 149.99 then '100-149'
when so.subtotal_amount between 150.00 and 249.99 then '150-249'
when so.subtotal_amount between 250.00 and 399.99 then '250-399'
when so.subtotal_amount between 400.00 and 499.99 then '400-499'
when so.subtotal_amount > 500 then '500+'
end)


It works well for me, but I would like to know if there is a way to NOT have to repeat that case statement. Can I set a variable or use an alias or something to stand in its place? I have to edit every instance of the case statement every time I make a tweak. I'm open to anything. Thanks.

Answer

This is the implementation of what @Uueerdo wrote in the comment.

Use an alias for your expression in the SELECT clause (as range). GROUP BY using that alias group by range. The expression can only be NULL if so.subtotal_amount IS NULL, so you can use this condition in the WHERE clause.

Your expression can also be shortened up. If the first condition (subtotal_amount < 100) fails, then it's clear that subtotal_amount is >= 100 so you don't need a BETWEEN statement. Note that BETWEEN means "including" in MySQL. x BETWEEN a and b is the same as x >= a and x <= b.

Also - when you "say" 'under 100' you should use < 100 instead of < 99.99. Same for '500+' - it should be >= 500. So you don't have any gaps in your ranges.

select 
    count(so.shop_order_id),
    case
        when so.subtotal_amount <  100 then 'under 100'
        when so.subtotal_amount <  150 then '100-149' 
        when so.subtotal_amount <  250 then '150-249'
        when so.subtotal_amount <  400 then '250-399' 
        when so.subtotal_amount <  500 then '400-499'
        when so.subtotal_amount >= 500 then '500+'
    end as range
from shop_order so
where so.created_at between '2015-11-29 23:59:59' and '2015-11-31 00:00:01'
  and so.subtotal_amount is not null
group by range