I'm thinking that something like this might work....
What do ya think? Yes? No? Maybe?
select to_char(Hour_of_Day,'HH AM'),Amount
from
(
select ba_timestamp Hour_of_Day,
sum(decode(ba_status,'bdone',decode (ba_price,null,0,ba_price),0))
Amount from bill_auth0 where ba_status in ('bdone') and srvc_prod_id =
'TOPUP' and merchant_id not in ('MoreMagic', 'cwcayman') and ba_channel
in
('SMS','WEB','WAP','ATM','J2ME')
group by ba_timestamp
)
order by Hour_of_Day;
-----Original Message-----
From: Kumar Pradip
[mailto:oracledba-ezmlmshield-x4288908.[Email address protected]
Sent: Thursday, January 27, 2005 10:30 AM
To: LazyDBA Discussion
Subject: RE: Help in SQL Query - TIMESTAMP
Can you post your complete text of your query?
You will see this error because of mismatch between select list and
group by.
Unlike Sybase Oracle (ANSI) won't let you select a column that is not
part of group by.
-----Original Message-----
From: subbarao
[mailto:oracledba-ezmlmshield-x2466965.[Email address protected]
Sent: Thursday, January 27, 2005 12:46 PM
To: LazyDBA Discussion
Subject: RE: Help in SQL Query - TIMESTAMP
Is it possible to use some thing like "order by
to_date(to_char(timestamp,'HH AM'))". I tried and getting errors.
"ORA-00937: not a single-group group function".
Quoting "Matthew W. Ball "
<oracledba-ezmlmshield-x17407879.[Email address protected]
> If I am recalling correctly, it will come back in ROWID order without
> an order by clause.
>
>
> On Thu, 2005-01-27 at 10:14 -0600, Bunke Bill C. wrote:
>
> > Technically, your query had no ORDER BY clause, so the result set
> > can be
in
> any order and the server would have done what was asked.
> >
> > Remember that while you may appear to see ordered results, without
> > ORDER
BY
> it is, in fact, strictly circumstantial.
> >
> > Also remember that although we as humans easily understand what
> DATE/DATETIME/TIMESTAMP represent (and can likewise instantly convert
> the text values shown in a query), a computer does not, and only sees
> what it is told. If you want the order to be date/time driven, you
> must exclude any data-formatting functions and simply use the internal
> date/time format for accurate results.
> >
> > HTH,
> > Bill
> >
> >
> > --------
> > website: http://www.LazyDBA.com
> > Please don't reply to RTFM questions Oracle documentation is here:
> > http://tahiti.oracle.com To unsubscribe: see
> > http://www.lazydba.com/unsubscribe.html
> > To subscribe: see http://www.lazydba.com By using this list you
> > agree to these
> terms:http://www.lazydba.com/legal.html
>
> ----------------------------------------------------------------
> |Matthew W. Ball (Matt)
> |Team Lead for Infrastructure and Reliability Group Endeavor
> |Information Systems Inc.
> |(e) [Email address protected]
> |(p) 847-227-2678
> |(f) 208-275-0869
> |(u) http://www.endinfosys.com
> --------------------------------------------------------------
> It is inaccurate to say I hate everything.
> I am strongly in favor of common sense, common honesty, and common
> decency.
> This makes me forever ineligible for public office.
> -H.L. Mencken, writer, editor, and critic (1880-1956)
> --------------------------------------------------------------
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
> see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com By using this list you agree
> to these terms:http://www.lazydba.com/legal.html
>
>
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html
========================================================================
======
This message is for the sole use of the intended recipient. If you
received
this message in error please delete it and notify us. If this message
was
misdirected, CSFB does not waive any confidentiality or privilege. CSFB
retains and monitors electronic communications sent through its network.
Instructions transmitted over this system are not binding on CSFB until
they
are confirmed by us. Message transmission is not guaranteed to be
secure.
========================================================================
======
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
Oracle LazyDBA home page