OLAP Functions - Rank by SUM(val) 1

[BrianTyler]

I am trying to understand some of the OLAP functions available in DB2.

Consider a table with three columns:

Customer, ProductCode, Value

I can get Value for each product for the customer, and the total value for the customer to appear in one result row:

select
Customer
,ProductCode
,Value
,sum(Value) over (partition by Customer) as TotValForCust
from
Sales


I am having trouble ranking the customers by total sales.

I Require:
Customer, ProductCode, Value, TotValForCust, RankbyTot

I cannot get the syntax correct - each variation I use throws out errors such as missing Group By etc.

I need something like

select
Customer
,ProductCode
,Value
,sum(Value) over (partition by Customer) as TotValForCust
,rank() over (order by sum(Value) over (partition by Customer) as RankbyTot
from
Sales

Any ideas?

 

[blom0344]

Brian,

The correct syntax should have the partition phrase first (if applicable) and then the order by

However, I do not know of it is allowed to reference an aggregate object in the order by.

Extensive list of examples can be found in SQL cookbooks.

Do a search on 'Graeme Birchall' and you will find his site with the formidable pdf books....

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[juliane26]

Well, Graeme Birchall's SQL Cookbook definitely is the best for DB2 SQL, I totally agree with Ties.

I am still not sure what you are looking for, but may be it is this :

select
cust,
prod,
val,
sum(val) over (partition by cust) as totalforcst,
rank() over (order by sum(val) over (partition by cust)) as rank
from t1

output:
CUST PROD VAL TOTALFORCST RANK
---- ---- ----------- ----------- --------------------
2 1 1 4 1
2 2 3 4 1
1 1 1 6 3
1 1 2 6 3
1 2 3 6 3

Is that what you need ?
It is more or less what you tried just adding some '(' and ')'.

That worked for me at least syntax wise on a windows UDB.

 

[BrianTyler]

Thanks to you both.

I certainly agree with your comments on Graeme's Cookbook - it's by far the best I've seen so far.

I think that Juliane's solution may do the job, otherwise I am confident that I can get what I want from an Outer Query to rank an Inner Query (temp table).

Regards
Brian