On Wed, 2010-04-28 at 08:57 +1000, Jesper Wisborg Krogh wrote:
> 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
>
Perfectamundo! I thought there would have been a more elegant way but
this works just fine. Thanks.
Keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
