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...


> 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

>

>

>