Re: SQL: Group BY + Order By

From: <ahandoko_at_sampoerna.co.id>
Date: Thu, 18 Jan 2001 09:53:10 +0700
Message-Id: <10744.126950@fatcity.com>

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

MAR 566 78678 instead of:

JAN      890    9899
FEB      897    8686
MAR    566    78678
APR      233     86999

AUG 1000 20100 How can I order it correctly? Thank in advance to you all.

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-L (or the name of mailing list you want to be removed from). You may Received on Wed Jan 17 2001 - 20:53:10 CST