On Wed, 2010-04-28 at 00:18 +0200, Carsten Pedersen wrote: > Keith Clark skrev: > > I have the following statement: > > > > select chart_of_accounts.accountname as Account, > > concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as > > Debit, > > concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as > > Credit, > > concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2)) > > as Balance > > from sales_journal_entries > > left join sales_journal > > on sales_journal.journalID=sales_journal_entries.journalID > > left join chart_of_accounts > > on chart_of_accounts.accountID=sales_journal_entries.accountID > > where sales_journal.date > '2008-12-31' > > and sales_journal.date < '2010-01-01' > > group by sales_journal_entries.accountID > > order by Balance asc; > > > > and I'd like the output to be sorted by the Balance according to the > > numberic value, but it is sorting by the string result. I tried > > abs(Balance) but I get the following error: > > > > 1247 Reference 'Balance' not supported (reference to group function) > > > > I'm not sure I understand the error. > > Balance is the result of a string operation (concat), and abs is a > numeric function that won't work on strings. > > You should add a field to the result with the numerical value of > Balance, and then sort on that. > > / Carsten > > You cannot CONVERT at the order by statement?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org