| ID | Name | min_amount | charge |
|---|---|---|---|
| 1 | Standard | 50.00 | 3.00 |
| 2 | Express | 50.00 | 5.00 |
| 3 | Standard | 100.00 | 2.00 |
| 4 | Express | 100.00 | 0.00 |
so I have this table in SQL and my requirement is to calculate shipping based on min_amount (subtotal). for eg: if the subtotal is 45.00 I want to get the row with ID 1,2 (because 45.00 < 50.00). and similarly if subtotal is 55 it should be the row with ID 3,4.
SELECT id,min_amount, MAX(charge) as shipping FROM `shippings` WHERE min_amount > 45.00 GROUP BY name
this is working. but when the value changes to 101.00, this returns nothing I want it to return the max min_amount row which is 3,4
what should I change? and what is the eloquent query for this, since I’m using laravel but can be done without it.
Advertisement
Answer
You need to get the highest min amount value first and use it to make a decision on upper value using IF condition in WHERE clause,
SELECT id, name, min_amount, MAX(charge) as shipping
FROM shippings,
(SELECT MAX(min_amount) as min_amount_limit FROM shippings) z
WHERE min_amount >= IF(z.min_amount_limit < 101.00, z.min_amount_limit, 101.00)
GROUP BY name;