Re: sum of a column (development question)

You lost me on that one. How would ntile apply when he just wants totals for this year and last year?

On 9/11/07, August Spier <gspier_at_chiliad.com> wrote:
>
> I was under the impression that NTILE would be more useful ... especially
> if you are in a development cycle and requirements for more sql statements
> are going to surface.
> See
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions101.htm#sthref1696
>
> r,
>
> Gus
>
> On Sep 11, 2007, at 3:16 PM, Stephane Faroult wrote:
>
> Lyal
>
> Wooops. This one should be correct (hopefully):
>
> select sum(case sign(inv_dt - trunc(sysdate, 'YEAR'))
> when 1 then ord_tot
> when 0 then ord_tot
> else 0
> end) TY,
> sum(case sign(inv_dt - trunc(sysdate, 'YEAR'))
> when -1 then ord_tot
> else 0
> end) LY from ar_inv_hdr
> where inv_dt between add_months(trunc(sysdate, 'YEAR'), -12) and sysdate
>
> Lyall Barbour wrote:
>
> Hi again,
> Development question. User wants the sum of all the purchase orders
> for this year and last year. I'm trying to get it in the same select
> statement. I want something like this, but don't know how to get it.
>
> select sum(ord_tot) "TY Invoiced Totals",
> sum(ord_tot) "LY Invoiced Totals"
> from ar_inv_hdr
> where inv_dt between trunc(sysdate, 'YYYY') and sysdate
> and inv_dt between trunc(sysdate, 'YYYY')-1 and trunc(sysdate,
> 'YYYY')-365
>
> This, obviously, doesn't do what i want, but it's where i've gotten to so
> far. Do i need to select from the same table twice?
>
> Thanks,
> Lyall
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l