469,636 Members | 1,511 Online

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

[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_spend with two columns, date and spend.

SELECT date, spend, sum(spend) over (order by date) as cumulative_spend
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
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

Apr 4 '06 #1
5 4194
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_Spend WHERE
Date BETWEEN '01.03.2006' AND '31.03.2006')
SELECT Date, Spend, Total/Spend Percentage
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

B.

Apr 4 '06 #2
Thanks - I'm not great on WITH myself, but that looks to make sense.

Currently we're trying

SELECT Date, Spend, spend/total Percentage
(SELECT SUM(Spend) total_spend FROM Advertising_Spend 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

Apr 4 '06 #3
[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_spend with two columns, date and spend.

SELECT date, spend, sum(spend) over (order by date) as cumulative_spend
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
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
Apr 4 '06 #4
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
WHERE date BETWEEN '01.03.2006' AND '31.03.2006') AS TMP;

Regards,
Miro

Apr 4 '06 #5
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
Apr 4 '06 #6

### This discussion thread is closed

Replies have been disabled for this discussion.