Re: sum of a column (development question)

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 11 Sep 2007 21:12:15 +0200
Message-ID: <46E6E88F.1060206@roughsea.com>

Lyall,

    Of course not.

    select sum(case sign(sysdate - trunc(sysdate, 'YEAR'))

                           when 1 then ord_tot
                           when 0 then ord_tot
                           else 0
                        end) TY,
                sum(case sign(sysdate - 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

Received on Tue Sep 11 2007 - 14:12:15 CDT