Sorting
Moderator: General Moderators
Sorting
Hi
Im trying to make a comparison chart for some currency rates at work and having a problem with sorting.
In the database i have records in the following format
company ( postoffice )
rate_desc ( EURO)
rate ( 1.114 )
delivery_range ( £500 ) <---min spend to qualify for free delivery otherwise delivery to be added
delivery_cost ( £5 ) <--- delivery if min amount not reached
company ( TRAVELfx )
rate_desc ( EURO)
rate ( 1.112 )
delivery_range ( £0 ) <---min spend to qualify for free delivery otherwise delivery to be added
delivery_cost ( £0 ) <--- delivery if min amount not reached
Now i have been pulling the data on rate but how can i calculate all delivery costs before display all data for best value for money? ie if someone was to buy £400 worth of euros they would be better off going with TRAVELfx ( as they offer free delivery ) but for £500 worth postoffice would be better as they then waive delivery ) i can do all the calculations but i want to know how to sort data accordingly
Im trying to make a comparison chart for some currency rates at work and having a problem with sorting.
In the database i have records in the following format
company ( postoffice )
rate_desc ( EURO)
rate ( 1.114 )
delivery_range ( £500 ) <---min spend to qualify for free delivery otherwise delivery to be added
delivery_cost ( £5 ) <--- delivery if min amount not reached
company ( TRAVELfx )
rate_desc ( EURO)
rate ( 1.112 )
delivery_range ( £0 ) <---min spend to qualify for free delivery otherwise delivery to be added
delivery_cost ( £0 ) <--- delivery if min amount not reached
Now i have been pulling the data on rate but how can i calculate all delivery costs before display all data for best value for money? ie if someone was to buy £400 worth of euros they would be better off going with TRAVELfx ( as they offer free delivery ) but for £500 worth postoffice would be better as they then waive delivery ) i can do all the calculations but i want to know how to sort data accordingly
Re: Sorting
So the math isleewad wrote:if someone was to buy £400 worth of euros they would be better off going with TRAVELfx ( as they offer free delivery ) but for £500 worth postoffice would be better as they then waive delivery )
Code: Select all
postoffice = (400 - 5) * 1.114 = 440.03
TRAVELfx = 400 * 1.112 = 444.8Code: Select all
postoffice = 500 * 1.114 = 557
TRAVELfx = 500 * 1.112 = 556Re: Sorting
Yes thats the math but need it in mysql
Re: Sorting
IF (the function) looks like a good place to start. "If there is a delivery range for the company and requested amount is less below it then use the requested amount minus the delivery cost in the calculation, else use the requested amount unaltered."
What does your SQL look like with that added?
What does your SQL look like with that added?
Re: Sorting
I have not got SQL for the above yet thats what i want help with, at minute i have it sorting by rate ( highest to lowest ) but i need some how add the delivery deductions subtracted before display them, maybe i should RSORT or something once i got all data from database
Re: Sorting
Sorting in PHP is not the best option.
You probably want to know the net converted amount, right? Do that in the SQL using the IF() explanation I gave (if you have problems with that then let us know what), give the column an alias, and ORDER BY it.
You probably want to know the net converted amount, right? Do that in the SQL using the IF() explanation I gave (if you have problems with that then let us know what), give the column an alias, and ORDER BY it.
Re: Sorting
Thanks requinix but i dont know what the query would be to be honest never used IF in mysql had a quick look in google but looks a bit complicated to me
Re: Sorting
i now have the following sql :
how do i add 'if' into it so delivery is only deducted if over the min_amount column?
Code: Select all
SELECT * , ( amount - delivery ) / rate AS SUM
FROM rates
WHERE currency = 'EUR'
ORDER BY SUMRe: Sorting
That link I gave tells you exactly how to use it.
So IF(delivery_range > 0, the net result after applying the delivery_cost, the result without applying a delivery cost).IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.
mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SELECT IF(1<2,'yes','no');
-> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.
Re: Sorting
Thanks but i have done it another way:
SELECT * , ( CASE WHEN ( amount < min_delivery )
THEN ( amount - delivery_fee )
ELSE amount
END
) * rate AS totalprice
FROM rates ORDER BY totalprice DESC
SELECT * , ( CASE WHEN ( amount < min_delivery )
THEN ( amount - delivery_fee )
ELSE amount
END
) * rate AS totalprice
FROM rates ORDER BY totalprice DESC
Re: Sorting
Same difference.