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

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