Re: sum of a column (development question)

Use decode or more flexible case expression to pick which values to sum (the normal technique for a pivot...)

select sum(case when inv_dt between trunc(sysdate, 'YYYY') and sysdate then ord_tot else null end) "TY Invoiced Totals",

       sum(case when inv_dt between add_months(trunc(sysdate, 'YYYY'),-12) and trunc(sysdate, 'YYYY')-1 then ord_tot else null end) "LY Invoiced Totals" from ar_inv_hdr
where inv_dt between trunc(sysdate, 'YYYY')-1 and sysdate

Possible issue: if inv_dt includes a time component, replace -1 with -0.05 in the second expression - this should evaluate to 22:48 on 31 Dec last year. Let's assume noone is posting invoices between then and midnight!

Regards Nigel

• Original Message ---- From: Lyall Barbour <lyallbarbour_at_sanfranmail.com> To: oracle-l_at_freelists.org Sent: Tuesday, September 11, 2007 6:32:37 PM Subject: sum of a column (development question)

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.

[snip]

--
http://www.freelists.org/webpage/oracle-l