Confused by OLAP functions

[James Foreman]

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 sales
(transactiondat e date,
productid int,
bookings int);

The first thing I want is a breakdown by productid of sales yesterday:

SELECT productid, sum(bookings)
FROM sales
WHERE transactiondate = current date - 1 days
GROUP BY productid;

So far, so easy. What I'm confused is how to get a breakdown by
productid of sales yesterday, and also show what percentage of sales
each productid contributed to the total -so I'd get a result set like
this:

PRODUCTID BKGS %AGE
1 4 50
2 2 25
3 2 25

I get the impression this would be by using PARTITION BY, but I can't
see quite how. (looking at an example on IBM's developerworks site, I
tried

SELECT productid, sum(bkgs) OVER (PARTITION BY productid) AS bkgsum,
(bkgs) / sum(bkgs) OVER (PARTITION BY product) AS percentage
FROM sales

but this doesn't work. Any pointers to a good syntax guide that isn't
vendor specific (if such a thing exists) that anyone can give me would
be greatly appreciated

[Serge Rielau]

That should do it:
SELECT productid,
bkgs,
(bkgs * 100) / count(bkgs) over () AS "%AGE"
FROM (
SELECT productid, sum(bookings) bkgs
FROM sales
WHERE transactiondate = current date - 1 days
GROUP BY productid)
AS T;
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

sum of course.. not count
Serge Rielau wrote:

That should do it:
SELECT productid,
bkgs, (bkgs * 100) / sum(bkgs) over () AS "%AGE" FROM (
SELECT productid, sum(bookings) bkgs
FROM sales
WHERE transactiondate = current date - 1 days
GROUP BY productid)
AS T;

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab