[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
FROM advertising_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
FROM advertising_spend
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