438,287 Members | 1,309 Online Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

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

 P: n/a [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 Apr 4 '06 #1
5 Replies

 P: n/a 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 FROM Advertising_Spend WHERE date BETWEEN '01.03.2006' AND '31.03.2006' B. Apr 4 '06 #2

 P: n/a 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_Spend, (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

 P: n/a 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_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? 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

 P: n/a 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_spend WHERE date BETWEEN '01.03.2006' AND '31.03.2006') AS TMP; Regards, Miro Apr 4 '06 #5

 P: n/a 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. 