Confused on how to get a percentage of total using OLAP functions

[James Conrad StJohn Foreman]

[8.2.4 on 32-bit linux]
Have found
http://www-128.ibm.com/developerwork.../0110lyle.html
which is quite helpful, but doesn't quite tell me what I want.

I have a table, advertising_spe nd with two columns, date and spend.

SELECT date, spend, sum(spend) over (order by date) as cumulative_spen d
FROM advertising_spe nd
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

gives me cumulative spend quite happily.

But how do I go about getting either the percentage of spend that each
day is of the total, or the percentage that the cumulative spend is of
the total?

Guessed at
select date, cost, sum(cost) over (order by date) as cumulative_sum,
cost/sum(cost) over (partition by date) percentage
FROM advertising_spe nd
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

which gives me 1 for each percentage, and tried removing the 'over
(partition by date)' part, but that gave me a syntax error, predictably
enough.

Is the percentage computable in one query, or do I need a second query
to provide the total spend, and then divide by that?

Thanks

JCSJF

[Brian Tkatch]

I'm going to guess. But perhaps WITH would be useful? (I'm not too
familiar with WITH.)

WITH Total(Spend) AS (SELECT SUM(Spend) FROM Advertising_Spe nd WHERE
Date BETWEEN '01.03.2006' AND '31.03.2006')
SELECT Date, Spend, Total/Spend Percentage
FROM Advertising_Spe nd
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

B.

[James Conrad StJohn Foreman]

Thanks - I'm not great on WITH myself, but that looks to make sense.

Currently we're trying

SELECT Date, Spend, spend/total Percentage
FROM Advertising_Spe nd,
(SELECT SUM(Spend) total_spend FROM Advertising_Spe nd WHERE
Date BETWEEN '01.03.2006' AND '31.03.2006') as a
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

but yours has at least the virtue of readability. Will bash this
through the optimiser and see if it's also more efficiently

[Serge Rielau]

James Conrad StJohn Foreman wrote:

[8.2.4 on 32-bit linux]
Have found
http://www-128.ibm.com/developerwork.../0110lyle.html
which is quite helpful, but doesn't quite tell me what I want.

I have a table, advertising_spe nd with two columns, date and spend.

SELECT date, spend, sum(spend) over (order by date) as cumulative_spen d
FROM advertising_spe nd
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

gives me cumulative spend quite happily.

But how do I go about getting either the percentage of spend that each
day is of the total, or the percentage that the cumulative spend is of
the total?

Guessed at
select date, cost, sum(cost) over (order by date) as cumulative_sum,
cost/sum(cost) over (partition by date) percentage
FROM advertising_spe nd
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

which gives me 1 for each percentage, and tried removing the 'over
(partition by date)' part, but that gave me a syntax error, predictably
enough.

Is the percentage computable in one query, or do I need a second query
to provide the total spend, and then divide by that?

Are you sure this query returns cumulative spent as is?
I think you need a clause to state that you the window includes all
preceeding, but only up to the current row.
Currently you get the total spent (and the ORDER BY is useless).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[mirof007]

Serge, IIRC, the default window spec in the presence of the ORDER BY
clause is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so yes,
he's getting a cumulative sum. Without the ORDER BY in the over()
clause the default window spec is the entire window (BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING).

James, to get percentage of total spend, try the following (haven't
checked for syntax errors, but hopefully it'll give you the idea):

SELECT date, spend, cume_spend, total_spend,
spend/total_spend as daily_percent,
cume_spend/total_spend as cume_percent
FROM ( SELECT date, spend,
sum(spend) over (order by date rows unbounded
preceding and current row) as cume_spend,
sum(spend) over (order by date rows unbounded
preceding and unbounded following) as total_spend
FROM advertising_spe nd
WHERE date BETWEEN '01.03.2006' AND '31.03.2006') AS TMP;

Regards,
Miro

[Serge Rielau]

mirof007 wrote:

Serge, IIRC, the default window spec in the presence of the ORDER BY
clause is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so yes,
he's getting a cumulative sum. Without the ORDER BY in the over()
clause the default window spec is the entire window (BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING).

Interesting.... tx Miro
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab