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@xxxxxxxxxxx> 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 > > > > > > -- > //www.freelists.org/webpage/oracle-l > > > > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'