473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
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 4346
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: James Foreman | last post by:
I want to do something that I'm sure is quite simple, but the DB2 SQL Reference isn't too clear on this (lots of examples of things that I don't want...) Assume a table like this: CREATE TABLE...
0
by: Angela | last post by:
I am using VS.NET 2003 and Crystal 9. I have been told by Crystal, as well as many co-workers who know Crystal really well, that I cannot create the particulat graph I need using Crystal Reports....
3
by: George Sakkis | last post by:
After a brief search, I didn't find any python package related to OLAP and pivot tables. Did I miss anything ? To be more precise, I'm not so interested in a full-blown OLAP server with an RDBMS...
0
by: YellowFin Announcements | last post by:
Yellowfin Reporting Announces Release 3 OLAP Connectivity New Features Including OLAP-to-Relational Drill Through Provide Customers with One Complete Web BI Tool for OLAP Analysis Yellowfin,...
0
by: YellowFin | last post by:
Yellowfin Announces Release 3 OLAP Connectivity New Features Including OLAP-to-Relational Drill Through Provide Customers with One Complete Web BI Tool for OLAP Analysis Yellowfin, today...
4
by: Micheal | last post by:
Greetings Access Group, Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days...
5
by: Aswanth | last post by:
I'm Using Asp.Net with C# & Working with SSRS 2005 for Generating Reports.. The Following Expression I'm using in Reports to Show the Percentage of Particular Items in REPORT.. ...
6
by: plaguna | last post by:
I’m creating a Microsoft Access Report of 6 different questions with “Yes” and “No” answers. I have no problem to count the Yes and Nos with the following formulas for each question in the Report...
1
by: sandeep.iitk | last post by:
Hi Friends, can anybody help me in rewriting below query with the help of OLAP functions. I never used OLAP functions , so need help in this regard. SELECT C.UNIT, C.DT from tab1 C where C.DT =...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.