On Wed, 28 Apr 2010 08:53:57 Keith Clark wrote:
> But I'd prefer not to see the extra sorting field.
You don't need to select a field in order to be able to order by it.
So
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
coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0)
asc;
should do the trick.
Jesper
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
