| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL: Group BY + Order By
I would simply add another column to the select to_char(value_date,'MM') as a NOPRINT column and sort off of that coulmn.
-----Original Message-----
From: ahandoko_at_sampoerna.co.id [SMTP:ahandoko_at_sampoerna.co.id] Sent: Wednesday, January 17, 2001 10:26 PM To: Multiple recipients of list ORACLE-L Subject: Re: SQL: Group BY + Order By
You should use the numeric for ordering them and then use "decode" to change to alphabetic. i.e: 1 = Jan, 2 = Feb. , etc.
That's the idea for your issue.
cheers,
Anies Handoko.
"Yuzie"
<tekait11_at_bni To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.co.id> cc:
Sent by: Subject: SQL: Group BY + Order By
root_at_fatcity.
com
18/01/2001
09:10
Please
respond to
ORACLE-L
Dear All,
I need to query columns (with a certain format) and pass the resultset to a
VB object (chart).
Column required are:
1. SUBSTR(TO_CHAR(value_date),4,3) -> will show month name (string), such
as
JAN, FEB, MAR, etc and will be used for X axis.
2. COUNT(reference) -> will show total number of transaction within a month
and will be used for Y axis
3. SUM(amount) -> will show total amount of transactions, and will be used
for second Y axis
My query statement is:
SELECT SUBSTR(TO_CHAR(value_date),4,3) AS MONTH, COUNT(reference) AS TRX,
SUM(amount) AS "TOTAL USD VALUE"
WHERE value_date >= '01-jan-2000' AND tglval <= '31-dec-2001'
GROUP BY SUBSTR(TO_CHAR(value_date),4,3).
The problem is it sorts the result alphabetically, so will give: AUG 1000 20100
APR 233 86999 FEB 897 8686 JAN 890 9899
JAN 890 9899 FEB 897 8686 MAR 566 78678 APR 233 86999
Yuzie
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yuzie INET: tekait11_at_bni.co.id Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Jan 18 2001 - 08:42:57 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: ahandoko_at_sampoerna.co.id Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
 |
 |