Message-ID: <46E6E971.4060903@roughsea.com>
Date: Tue, 11 Sep 2007 21:16:01 +0200
From: Stephane Faroult <sfaroult@roughsea.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: lyallbarbour@sanfranmail.com
Cc: oracle-l@freelists.org
Subject: Re: sum of a column (development question)
References: <20070911173238.4B8D11CE303@ws1-6.us4.outblaze.com>
In-Reply-To: <20070911173238.4B8D11CE303@ws1-6.us4.outblaze.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: sfaroult@roughsea.com

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