Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql query
It would do more obviously if written like
select
'Q'||to_char(trunc(order_date,'Q'),'Q')||' '||to_char(trunc(order_date,'Y'),'YYYY') quarter,
count(*) orders
from orddet
group by 'Q'||to_char(trunc(order_date,'Q'),'Q')||' '||to_char(trunc(order_date,'Y'),'YYYY');
Which gives
QUARTER ORDERS
------- ----------
Q1 1996 3 Q2 1996 3 Q3 1996 3 Q4 1996 3
on a sample table with one row for each month of 1996. No idea about performance of such query though. Can it be that equivalent query with analytic count(*) over ( partition by trunc(order_date,'Q') ) would do better on 100m rows?
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1024433884.20070.1.nnrp-12.9e984b29_at_news.demon.co.uk...Received on Wed Jun 19 2002 - 09:36:28 CDT
> You beat me to it.
>
> There is also the option for TRUNC() truncating
> the date to the quarter - which might be faster.
>
> However, the format MIGHT be 'QQ', and there
> is the problem that the dates that Oracle thinks
> are the first of each quarter may not be the ones
> required by the poster.
>
> Something like:
> select trunc(date_col, 'QQ') , count(*)
> from big_table
> group by trunc(date_col, 'QQ')
> ;
>
> should do it.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK June / July
> Australia July / August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Sybrand Bakker wrote in message ...
> >
> >>
> >IIRC the to_char function still support the Q format model, where q is
> >for quarter.
> >
> >Hth
> >
> >Sybrand Bakker, Senior Oracle DBA
>
>
>