order by numeric value

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.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: order by numeric value

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: order by numeric value

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=gcdmg-mysql-2@m.gmane.org

Re: order by numeric value

--COMODO_27_Apr_2010_16_46_16
Content-Type: text/plain;
format=flowed;
charset="utf-8";
reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005

Try

order by CAST(Balance as decimal(8,2)) asc;


Cast will work in the order by.

Glenn Vaughn

----- Original Message -----
From: "Keith Clark"
To:
Sent: Tuesday, April 27, 2010 3:52 PM
Subject: order by numeric value


>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.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@dawiz.net

--COMODO_27_Apr_2010_16_46_16
Content-Type: application/x-pkcs7-signature;
name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="smime.p7s"

MIIHwgYJKoZIhvcNAQcCoIIHszCCB68CAQExCzAJBgUrDgMCGgUAMAsGCSqG SIb3DQEHAaCCBaQw
ggWgMIIEiKADAgECAhAYAofBaR8Q9AvLXcnn6IwMMA0GCSqGSIb3DQEBBQUA MIGuMQswCQYDVQQG
EwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4w HAYDVQQKExVUaGUg
VVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRy dXN0LmNvbTE2MDQG
A1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5k IEVtYWlsMB4XDTEw
MDIwNzAwMDAwMFoXDTExMDIwNzIzNTk1OVowgdcxNTAzBgNVBAsTLENvbW9k byBUcnVzdCBOZXR3
b3JrIC0gUEVSU09OQSBOT1QgVkFMSURBVEVEMUYwRAYDVQQLEz1UZXJtcyBh bmQgQ29uZGl0aW9u
cyBvZiB1c2U6IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8w HQYDVQQLExYoYyky
MDAzIENvbW9kbyBMaW1pdGVkMRUwEwYDVQQDEwxHbGVubiBWYXVnaG4xHjAc BgkqhkiG9w0BCQEW
D2Rhd2l6QGRhd2l6Lm5ldDCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA qeEKqGBc3IHvmusy
zaMLGVCGwip17ktPRnC+siNJPJ/EGyXhxCUdEVti//dNVZ86qI1g7SGKB54d VV73Hj61RN0TL6i9
gLmaaVjsyQWZBTolrb8gC4WB0rH823dt4P2ALrflClB6u0BfOiBd0kE4egR3 ubEW5Q7KsxWOgu0i
5ocCAwEAAaOCAhEwggINMB8GA1UdIwQYMBaAFImCZ33EnSZwAEu0UEh83j2u BG59MB0GA1UdDgQW
BBSRkYvQ6bryEW06yrZwEN9tUCNbEjAOBgNVHQ8BAf8EBAMCBaAwDAYDVR0T AQH/BAIwADAgBgNV
HSUEGTAXBggrBgEFBQcDBAYLKwYBBAGyMQEDBQIwEQYJYIZIAYb4QgEBBAQD AgUgMEYGA1UdIAQ/
MD0wOwYMKwYBBAGyMQECAQEBMCswKQYIKwYBBQUHAgEWHWh0dHBzOi8vc2Vj dXJlLmNvbW9kby5u
ZXQvQ1BTMIGlBgNVHR8EgZ0wgZowTKBKoEiGRmh0dHA6Ly9jcmwuY29tb2Rv Y2EuY29tL1VUTi1V
U0VSRmlyc3QtQ2xpZW50QXV0aGVudGljYXRpb25hbmRFbWFpbC5jcmwwSqBI oEaGRGh0dHA6Ly9j
cmwuY29tb2RvLm5ldC9VVE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0 aW9uYW5kRW1haWwu
Y3JsMGwGCCsGAQUFBwEBBGAwXjA2BggrBgEFBQcwAoYqaHR0cDovL2NydC5j b21vZG9jYS5jb20v
VVROQUFBQ2xpZW50Q0EuY3J0MCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5j b21vZG9jYS5jb20w
GgYDVR0RBBMwEYEPZGF3aXpAZGF3aXoubmV0MA0GCSqGSIb3DQEBBQUAA4IB AQA4/FYusHK6TwTB
CQmHstLcPjFhB0he5a67WypFizjKysPFCODPxatrW0oSaDG1ki45ZeBQuvPa zK0NADUXXq2QtYSs
f0smgNW9B2XvYRyY4Jg7JNYVd2QEnp3Rdu0ruVwlEkj+ViAvQAy/4BXx5QTl EX15+K4SC3agnzOt
pECqTfBtCs5uj5J+dORCBeLDxCnoVl2X6ZkMVL8OlrimolVY7Qhfx1FUDW9K RMw31T83cVAVktvW
pHFn+LJIAscp+HTkUbBHyWMtkmEWuBApVU3pJoqvmj/4GC4Kh5vcy59ezN9v zs9MdhDR4v2eszxw
5F40U7OTVZGwUmFAdJ29PedfMYIB5jCCAeICAQEwgcMwga4xCzAJBgNVBAYT AlVTMQswCQYDVQQI
EwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBV U0VSVFJVU1QgTmV0
d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYwNAYD VQQDEy1VVE4tVVNF
UkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwCEBgCh8Fp HxD0C8tdyefojAww
CQYFKw4DAhoFAKB6MBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwGwYJKoZI hvcNAQkPMQ4wDDAK
BggqhkiG9w0DBzAcBgkqhkiG9w0BCQUxDxcNMTAwNDI3MjI0NjE2WjAjBgkq hkiG9w0BCQQxFgQU
RgYOcvUbNE9aMPGBzDoIBY8rsGMwDQYJKoZIhvcNAQEBBQAEgYASZed/7qzb 35qtfFG2/Z6ur/HE
bVUZOB42hY6C4/SiV2GM+4yIl25AGkucznjwJrb3PbjtAnF8GT1q6/CUo2Bt Akzt1tja2FHsibJ7
gcf+kqo+FJkTSFXsGApclc6POgguFeSFFoZAb7pvAHTfumgAT95sokwiJ4yP NtgL3namJA==

--COMODO_27_Apr_2010_16_46_16--

RE: order by numeric value

VGhhdCB3b24ndCB3b3JrIG9uIGEgdmFsdWUgbGlrZSAiJDEuMDAiDQoNCnNl bGVjdCBDQVNUKCck
MS4wMCcgYXMgZGVjaW1hbCg4LDIpKTsNCistLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0t
Kw0KfCBDQVNUKCckMS4wMCcgYXMgZGVjaW1hbCg4LDIpKSB8DQorLS0tLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLSsNCnwgICAgICAgICAgICAgICAgICAgICAgICAgIDAu MDAgfA0KKy0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rDQoxIHJvdyBpbiBzZXQsIDEg d2FybmluZyAoMC4w
MCBzZWMpDQoNCg0KKy0tLS0tLS0tLSstLS0tLS0rLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0rDQp8IExldmVsICAgfCBDb2RlIHwgTWVzc2Fn ZSAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgIHwNCistLS0tLS0tLS0rLS0tLS0t Ky0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tKw0KfCBXYXJuaW5n IHwgMTI5MiB8IFRy
dW5jYXRlZCBpbmNvcnJlY3QgREVDSU1BTCB2YWx1ZTogJyQxLjAwJyB8DQor LS0tLS0tLS0tKy0t
LS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLSsNCjEgcm93
IGluIHNldCAoMC4wMCBzZWMpDQoNCg0KSXQgd291bGQgaGF2ZSB0byBiZSBz b21ldGhpbmcgbGlr
ZToNCg0Kc2VsZWN0IENBU1QoUkVQTEFDRSgnJDEuMDAnLCckJywnJykgYXMg ZGVjaW1hbCg4LDIp
KSA7DQorLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0rDQp8
IENBU1QoUkVQTEFDRSgnJDEuMDAnLCckJywnJykgYXMgZGVjaW1hbCg4LDIp KSB8DQorLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rDQp8 ICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgMS4wMCB8DQorLS0tLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rDQoxIHJvdyBpbiBzZXQg KDAuMDAgc2VjKQ0K
DQoNCldoaWNoIGluIHRoYXQgY2FzZSwgaXQncyBiZXR0ZXIgdG8ganVzdCBz ZWxlY3QgYmFsYW5j
ZSB3aXRob3V0IHRoZSBkb2xsYXIgc2lnbiBhbmQgb3JkZXIgb24gdGhhdCBj b2x1bW4uDQoNClJl
Z2FyZHMsDQpHYXZpbiBUb3dleQ0KDQotLS0tLU9yaWdpbmFsIE1lc3NhZ2Ut LS0tLQ0KRnJvbTog
RGFXaXogW21haWx0bzpkYXdpekBkYXdpei5uZXRdDQpTZW50OiBUdWVzZGF5 LCBBcHJpbCAyNywg
MjAxMCAzOjQ2IFBNDQpUbzogS2VpdGggQ2xhcms7IG15c3FsQGxpc3RzLm15 c3FsLmNvbQ0KU3Vi
amVjdDogUmU6IG9yZGVyIGJ5IG51bWVyaWMgdmFsdWUNCg0KVHJ5DQoNCm9y ZGVyIGJ5IENBU1Qo
QmFsYW5jZSBhcyBkZWNpbWFsKDgsMikpIGFzYzsNCg0KDQpDYXN0IHdpbGwg d29yayBpbiB0aGUg
b3JkZXIgYnkuDQoNCkdsZW5uIFZhdWdobg0KDQotLS0tLSBPcmlnaW5hbCBN ZXNzYWdlIC0tLS0t
DQpGcm9tOiAiS2VpdGggQ2xhcmsiIDxrZWl0aGNsYXJrQGstd2Jvb2t3b3Jt LmNvbT4NClRvOiA8
bXlzcWxAbGlzdHMubXlzcWwuY29tPg0KU2VudDogVHVlc2RheSwgQXByaWwg MjcsIDIwMTAgMzo1
MiBQTQ0KU3ViamVjdDogb3JkZXIgYnkgbnVtZXJpYyB2YWx1ZQ0KDQoNCj5J IGhhdmUgdGhlIGZv
bGxvd2luZyBzdGF0ZW1lbnQ6DQo+DQo+IHNlbGVjdCBjaGFydF9vZl9hY2Nv dW50cy5hY2NvdW50
bmFtZSBhcyBBY2NvdW50LA0KPiBjb25jYXQoJyQnLGZvcm1hdChjb2FsZXNj ZShzdW0oc2FsZXNf
am91cm5hbF9lbnRyaWVzLmRlYml0KSwwKSwyKSkgYXMNCj4gRGViaXQsDQo+ IGNvbmNhdCgnJCcs
Zm9ybWF0KGNvYWxlc2NlKHN1bShzYWxlc19qb3VybmFsX2VudHJpZXMuY3Jl ZGl0KSwwKSwyKSkg
YXMNCj4gQ3JlZGl0LA0KPiBjb25jYXQoJyQnLGZvcm1hdChjb2FsZXNjZShz dW0oc2FsZXNfam91
cm5hbF9lbnRyaWVzLmNyZWRpdCksMCktY29hbGVzY2Uoc3VtKHNhbGVzX2pv dXJuYWxfZW50cmll
cy5kZWJpdCksMCksMikpDQo+IGFzIEJhbGFuY2UNCj4gZnJvbSBzYWxlc19q b3VybmFsX2VudHJp
ZXMNCj4gbGVmdCBqb2luIHNhbGVzX2pvdXJuYWwNCj4gb24gc2FsZXNfam91 cm5hbC5qb3VybmFs
SUQ9c2FsZXNfam91cm5hbF9lbnRyaWVzLmpvdXJuYWxJRA0KPiBsZWZ0IGpv aW4gY2hhcnRfb2Zf
YWNjb3VudHMNCj4gb24gY2hhcnRfb2ZfYWNjb3VudHMuYWNjb3VudElEPXNh bGVzX2pvdXJuYWxf
ZW50cmllcy5hY2NvdW50SUQNCj4gd2hlcmUgc2FsZXNfam91cm5hbC5kYXRl ID4gJzIwMDgtMTIt
MzEnDQo+IGFuZCBzYWxlc19qb3VybmFsLmRhdGUgPCAnMjAxMC0wMS0wMScN Cj4gZ3JvdXAgYnkg
c2FsZXNfam91cm5hbF9lbnRyaWVzLmFjY291bnRJRA0KPiBvcmRlciBieSBC YWxhbmNlIGFzYzsN
Cj4NCj4gYW5kIEknZCBsaWtlIHRoZSBvdXRwdXQgdG8gYmUgc29ydGVkIGJ5 IHRoZSBCYWxhbmNl
IGFjY29yZGluZyB0byB0aGUNCj4gbnVtYmVyaWMgdmFsdWUsIGJ1dCBpdCBp cyBzb3J0aW5nIGJ5
IHRoZSBzdHJpbmcgcmVzdWx0LiAgSSB0cmllZA0KPiBhYnMoQmFsYW5jZSkg YnV0IEkgZ2V0IHRo
ZSBmb2xsb3dpbmcgZXJyb3I6DQo+DQo+IDEyNDcgUmVmZXJlbmNlICdCYWxh bmNlJyBub3Qgc3Vw
cG9ydGVkIChyZWZlcmVuY2UgdG8gZ3JvdXAgZnVuY3Rpb24pDQo+DQo+IEkn bSBub3Qgc3VyZSBJ
IHVuZGVyc3RhbmQgdGhlIGVycm9yLg0KPg0KPg0KPiAtLQ0KPiBNeVNRTCBH ZW5lcmFsIE1haWxp
bmcgTGlzdA0KPiBGb3IgbGlzdCBhcmNoaXZlczogaHR0cDovL2xpc3RzLm15 c3FsLmNvbS9teXNx
bA0KPiBUbyB1bnN1YnNjcmliZTogICAgaHR0cDovL2xpc3RzLm15c3FsLmNv bS9teXNxbD91bnN1
Yj1teXNxbEBkYXdpei5uZXQNCg0KVGhpcyBtZXNzYWdlIGNvbnRhaW5zIGNv bmZpZGVudGlhbCBp
bmZvcm1hdGlvbiBhbmQgaXMgaW50ZW5kZWQgb25seSBmb3IgdGhlIGluZGl2 aWR1YWwgbmFtZWQu
ICBJZiB5b3UgYXJlIG5vdCB0aGUgbmFtZWQgYWRkcmVzc2VlLCB5b3UgYXJl IG5vdGlmaWVkIHRo
YXQgcmV2aWV3aW5nLCBkaXNzZW1pbmF0aW5nLCBkaXNjbG9zaW5nLCBjb3B5 aW5nIG9yIGRpc3Ry
aWJ1dGluZyB0aGlzIGUtbWFpbCBpcyBzdHJpY3RseSBwcm9oaWJpdGVkLiAg UGxlYXNlIG5vdGlm
eSB0aGUgc2VuZGVyIGltbWVkaWF0ZWx5IGJ5IGUtbWFpbCBpZiB5b3UgaGF2 ZSByZWNlaXZlZCB0
aGlzIGUtbWFpbCBieSBtaXN0YWtlIGFuZCBkZWxldGUgdGhpcyBlLW1haWwg ZnJvbSB5b3VyIHN5
c3RlbS4gRS1tYWlsIHRyYW5zbWlzc2lvbiBjYW5ub3QgYmUgZ3VhcmFudGVl ZCB0byBiZSBzZWN1
cmUgb3IgZXJyb3ItZnJlZSBhcyBpbmZvcm1hdGlvbiBjb3VsZCBiZSBpbnRl cmNlcHRlZCwgY29y
cnVwdGVkLCBsb3N0LCBkZXN0cm95ZWQsIGFycml2ZSBsYXRlIG9yIGluY29t cGxldGUsIG9yIGNv
bnRhaW4gdmlydXNlcy4gVGhlIHNlbmRlciB0aGVyZWZvcmUgZG9lcyBub3Qg YWNjZXB0IGxpYWJp
bGl0eSBmb3IgYW55IGxvc3Mgb3IgZGFtYWdlIGNhdXNlZCBieSB2aXJ1c2Vz IG9yIGVycm9ycyBv
ciBvbWlzc2lvbnMgaW4gdGhlIGNvbnRlbnRzIG9mIHRoaXMgbWVzc2FnZSwg d2hpY2ggYXJpc2Ug
YXMgYSByZXN1bHQgb2YgZS1tYWlsIHRyYW5zbWlzc2lvbi4gW0ZyaWVuZEZp bmRlciBOZXR3b3Jr
cywgSW5jLiwgMjIwIEh1bWJvbHQgY291cnQsIFN1bm55dmFsZSwgQ0EgOTQw ODksIFVTQSwgRnJp
ZW5kRmluZGVyLmNvbQ0K

RE: order by numeric value

But I'd prefer not to see the extra sorting field.

Keith

On Tue, 2010-04-27 at 15:49 -0700, Gavin Towey wrote:
> That won't work on a value like "$1.00"
>
> select CAST('$1.00' as decimal(8,2));
> +-------------------------------+
> | CAST('$1.00' as decimal(8,2)) |
> +-------------------------------+
> | 0.00 |
> +-------------------------------+
> 1 row in set, 1 warning (0.00 sec)
>
>
> +---------+------+------------------------------------------ --+
> | Level | Code | Message |
> +---------+------+------------------------------------------ --+
> | Warning | 1292 | Truncated incorrect DECIMAL value: '$1.00' |
> +---------+------+------------------------------------------ --+
> 1 row in set (0.00 sec)
>
>
> It would have to be something like:
>
> select CAST(REPLACE('$1.00','$','') as decimal(8,2)) ;
> +-----------------------------------------------+
> | CAST(REPLACE('$1.00','$','') as decimal(8,2)) |
> +-----------------------------------------------+
> | 1.00 |
> +-----------------------------------------------+
> 1 row in set (0.00 sec)
>
>
> Which in that case, it's better to just select balance without the dollar sign and order on that column.
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: DaWiz [mailto:dawiz@dawiz.net]
> Sent: Tuesday, April 27, 2010 3:46 PM
> To: Keith Clark; mysql@lists.mysql.com
> Subject: Re: order by numeric value
>
> Try
>
> order by CAST(Balance as decimal(8,2)) asc;
>
>
> Cast will work in the order by.
>
> Glenn Vaughn
>
> ----- Original Message -----
> From: "Keith Clark"
> To:
> Sent: Tuesday, April 27, 2010 3:52 PM
> Subject: order by numeric value
>
>
> >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.
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@dawiz.net
>
> This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt
court, Sunnyvale, CA 94089, USA, FriendFinder.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: order by numeric value

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(s ales_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=gcdmg-mysql-2@m.gmane.org

Re: order by numeric value

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(s ales_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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: order by numeric value

>> > 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(s ales_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.

There is a more elegant way: do not do your $ at the database, but rather
in the presentation layer.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org