So if you have a table that has a date time type and you want to sum
the qty by account but want to do it in such a way that it fetches the
data once first and then aggregates over different periods how do you
use say rollup group by if you want to sum last hour and last 24
hours? The idea is to simply get a sum by account for the last hour
and last 24 hours.

presumably there is an approach that is more efficient than doing two
separate aggregations as components to a unionall and then merging
those two. Where the first set qualifies the data by sysdate - 1/24
(last hour) and the second set qualifies by sysdate - 1

On 03.06.2010 13:03, Robert Nicholson wrote:
You can do something like (pseudo SQL):

assuming a column "ts" of type DATE, but the concept would work with
TIMESTAMP types as well.

select account
, sum(case when ts >= sysdate - 1 then qty else 0 end) as qty_last_hr
, sum(case when ts >= sysdate - 1/24 then qty else 0 end) as qty_last_24
where ts >= sysdate - 1
group by account

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 03.06.2010 14:29, Robert Klemme wrote:
Oops, naming of columns was wrong. And with the WHERE clause one CASE
is superfluous:

select account
, sum(qty) as qty_last_24
, sum(case when ts >= sysdate - 1/24 then qty else 0 end) as qty_last_hr
where ts >= sysdate - 1
group by account

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Jun 3, 4:03*am, Robert Nicholson <robert.nichol... (AT) gmail (DOT) com>
wrote:
Also see http://www.orafusion.com/art_anlytc.htm under the section
"Windowing aggregate functions:"

(I've seen this type of discursion in many places, that's just the
first that caught my attention googling date aggregation Oracle)

Depending on what else you are doing in the sql and what everyone else
is doing, sometimes the sum is better, sometimes the analytics. If it
is something done heavily or often, it's worth it to try different
ways under load.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...pleads-guilty/