Bartonb12 - 1 year ago 62

MySQL Question

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 Source

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
```