Re: sum of a column (development question)

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Tue, 11 Sep 2007 13:03:35 -0500 (CDT)
Message-ID: <20347.12.17.117.251.1189533815.squirrel@12.17.117.251>

Hi Lyall,

Recent versions of Oracle will support this syntax:

SELECT

SUM(CASE TO_CHAR(inv_td,'YYYY') WHEN TO_CHAR(SYSDATE,'YYYY') THEN ord_tot
ELSE NULL END) "TY Tot",
SUM(CASE TO_CHAR(inv_td,'YYYY') WHEN

TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-1) THEN ord_tot ELSE NULL END) "LY Tot"
FROM ar_inv_hdr;

There might be a better way for last year -- this is off the top of my head.

HTH! GL! Rich

> 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 - 13:03:35 CDT