Hi Chris:
Thanks for the help. It worked. I realized very late that the group by clause
and the 1st column of the query are not matching. Thanks for pointing to that.
I have one more question:
From the result set below, for the DATE "01-06-2005" , records with time
"12:XX:XX PM" are displayed after the records with time "01:53:03 PM" and etc..
Should't we see records with 12 PM first and then records with 1 PM , 2 PM , 3
PM and so on later?
Also, I thought, the records will be in order by date. In that case, I should
see the latest records first and then previuos dates one later and so on. In
that case, I should see records of the DATE 14th Jan'2005 first , then 06th
Jan'2005 next , 23rd Dec'2004 next and 22nd Dec'2004 being last. But, below
results show in a different way? How do we get the results in proper sequence by
DATE/TIMESTAMP?
I tried using the order by clause with both "asc" and "desc" and I could not see
the results I wanted. Appreciate any help regarding this.
SQL> select to_char(ba_timestamp,'MM-DD-YYYY HH:MI:SS AM') Hour_of_Day,
2 sum(decode(ba_status,'bdone',decode (ba_price,null,0,ba_price),0)) Amount
3 from bill_auth0 where ba_status in ('bdone') and srvc_prod_id = 'TOPUP' and
4 merchant_id not in ('MoreMagic', 'cwcayman') and ba_channel in
5 ('SMS','WEB','WAP','ATM','J2ME')
6 group by to_char(ba_timestamp,'MM-DD-YYYY HH:MI:SS AM'); 5 6
HOUR_OF_DAY AMOUNT
---------------------- ----------
01-06-2005 01:53:03 PM 5.22
01-06-2005 03:12:13 PM 5
01-06-2005 03:22:07 PM 10
01-06-2005 03:24:09 PM 5.1
01-06-2005 03:25:04 PM 5
01-06-2005 03:26:09 PM 10.5
01-06-2005 12:18:21 PM 10
01-06-2005 12:24:21 PM 5
01-06-2005 12:45:55 PM 5.22
01-06-2005 12:52:56 PM 5.22
01-06-2005 12:52:57 PM 5
HOUR_OF_DAY AMOUNT
---------------------- ----------
01-06-2005 12:52:58 PM 5
01-14-2005 01:58:08 PM 10
01-14-2005 02:16:17 PM 10
01-14-2005 02:16:35 PM 34
01-14-2005 02:16:51 PM 123
01-14-2005 02:17:31 PM 120
01-14-2005 02:17:39 PM 30
01-14-2005 02:17:50 PM 110
01-14-2005 04:22:34 PM 12
01-14-2005 04:22:50 PM 111
01-14-2005 04:22:59 PM 120
HOUR_OF_DAY AMOUNT
---------------------- ----------
01-14-2005 04:23:25 PM 20
12-22-2004 02:34:17 PM 5
12-22-2004 10:53:00 AM 5
12-22-2004 11:49:05 AM 5.01
12-22-2004 11:51:46 AM 10
12-22-2004 12:06:38 PM 5
12-22-2004 12:08:00 PM 5
12-22-2004 12:14:23 PM 10
12-22-2004 12:16:53 PM 5
12-22-2004 12:19:22 PM 5.02
12-22-2004 12:23:43 PM 5
HOUR_OF_DAY AMOUNT
---------------------- ----------
12-22-2004 12:25:14 PM 5.22
12-23-2004 03:19:31 PM 13.27
35 rows selected.
Oracle LazyDBA home page