Thanks, it is solved!!!
- Original Message -----
From: "Dr Holden" <drholden_at_mediaone.net>
To: "Yuzie" <tekait11_at_bni.co.id>; "Oracle Mailinglist"
<oracledba_at_quickdoc.co.uk>; "Oracle Lazy DBA" <oracledba_at_lazydba.com>;
"Oracle fatcity" <ORACLE-L_at_fatcity.com>
Sent: Thursday, January 18, 2001 9:43 AM
Subject: RE: Group BY + Order By
> Try this, it should do it for you...
>
> Dean
>
>
> select month, trx, tuv "TOTAL USD VALUE"
> from (
>
> SELECT TO_CHAR(value_date,'MM'),
> TO_CHAR(value_date,'MON') month,
> COUNT(reference) trx,
> SUM(amount) tuv
> WHERE value_date >= '01-jan-2000'
> AND tglval <= '31-dec-2001'
> GROUP BY
> TO_CHAR(value_date,'MM'),
> TO_CHAR(value_date,'MON')
> )
>
>
>
>
>
> -----Original Message-----
> From: Yuzie [mailto:tekait11_at_bni.co.id]
> Sent: Wednesday, January 17, 2001 9:13 PM
> To: Oracle Mailinglist; Oracle Lazy DBA; Oracle fatcity
> Subject: SQL: Group BY + Order By
>
>
> 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
>
>
>
>
> --------
> Think you know someone who can answer the above question? Forward it to
> them!
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
> to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com
Received on Wed Jan 17 2001 - 21:20:11 CST
