Re: sum of a column (development question)
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: lyallbarbour@xxxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Sep 2007 11:20:47 -0700 (PDT)
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@xxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
