Re: 2nd attempt: Window function SQL - can't quite figure it.

Thanks again - that works nicely.

One final question follows on from my original question.

This query (using "traditional" SQL) works:

SELECT t1.dep, t1.man,
t1.deptot,
t1.stot_per_man,
ROUND((t1.stot_per_man/t1.deptot * 100), 2) AS "%age sales per manager"
FROM
(
SELECT s1.department AS dep, s1.salesmanager AS man,
(SELECT sum(s2.amount) FROM sales s2 WHERE s2.department =
s1.department) AS deptot,
(SELECT sum(s3.amount) FROM sales s3 WHERE s3.salesmanager =
s1.salesmanager) AS stot_per_man
-- stot_per_man/deptot AS "%age sales per manager"
FROM sales s1
GROUP BY s1.department, s1.salesmanager
) AS t1

Note the commented line -- stot_per_man/deptot AS "%age sales per manager"

If I uncomment it, I get "ERROR: column "stot_per_man" does not exist"

Now, why can't I use deptot and stot_per_man aliases in my query? It would
greatly simplify the SQL by removing the need for a subquery (or inline view
as Oracle calls them).

Thanks again,

Pál.

> David J.

--

--

Pál Teleki