I need to format numbers in mySQL so that they are rounded to 0 decimal places and also have the commas in the right place. (ie: 24351.0433 -> 24,351) Unfortunately, FORMAT() returns a string so when it is sorted asc/desc it treats it like it is text. (ie: 30,25,8 would be sorted ascending as 25,30,8)
ROUND() worked well except it did not insert the commas for the thousands place. Any thoughts?
Here's one way:
SELECT FORMAT(num, 0) AS n FROM mytable ORDER BY LPAD(n, 10, '0')Edit: Here's a better way:
SELECT FORMAT(num, 0) AS n FROM mytable ORDER BY ABS(num)Unfortunately, I need to do a COUNT with an IF statement in order to get the number. I get an error if I put it in the ORDER BY...
OK...I missed that part.
Can you post what you've tried?
Well...I figured out that I could select the data twice in the same query. Then, format one of the selections and sort by the other. It works, but is more resource intensive. The age old battle between form and function...
Thank you for your help. Any further thoughts are welcome.
Can you show the query you've ended up with, because unless you're deriving the number formatted by some hokeyness why not just sort on that in the first place.