Formatted number column cannot be sorted by number

Question posted by: dlite922 (Site Addict) on June 25th, 2008 02:39 AM

This is again from the dynamic reporting module i'm building.

Columns come in and are built based on XML doc and the HTML (smarty template) does not know weather this is a number to format it nicely such as 2345.993 to 2,346.99.

So I do my formatting in the XML which gives the dynamic query developer something similar this:

FORMAT(price * qty,2) AS sales

All was fine and dandy until i wanted to sort it by number. Turns out Format makes it as string and it does a string sort, in other words it would sort it like this:

1223
21
90

So a genius (/sarcasm) told me to "add a zero" to it and it would work.

nope, it turned 2,345.00 into 2.00. (had to explain to the client why his biggest sale was showing up as 2.00)

Now i've converted it to ROUND()

but is there any other way to have the comma (thousands delimiter) as well has have mySQL treat it as a number?

Thanks for your feedback,


Dan

Not the answer you were looking for? Post your question . . .
189,815 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).