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
