Re: sum of a column (development question)

  • From: August Spier <gspier@xxxxxxxxxxx>
  • To: sfaroult@xxxxxxxxxxxx
  • Date: Tue, 11 Sep 2007 17:39:09 -0400

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2007