Thanks for that - I managed > SELECT *, total_mgr_dept / total_dept_only AS manager_share_of_department > <rest of query snipped...>
I managed to do using two CTEs as shown below.
I was wondering, how would it be possible to do what I want using traditional SQL? That is, no CTE's and no Windowing functions?
====== My effort using two CTE's ====
WITH tab1 AS ( SELECT department, salesmanager, SUM(amount) as sm1 FROM sales GROUP BY department, salesmanager ), tab2 AS ( SELECT department, SUM(amount) as sm2 FROM sales GROUP BY department ) SELECT tab1.*, tab2.sm2, ROUND((tab1.sm1/tab2.sm2) * 100, 2) AS "%age of Dept. income" FROM tab1 INNER JOIN tab2 ON tab1.department = tab2.department;
Correlated subqueries.
Not tested, should at least give an idea even if it has an error
SELECT s1.department, s1.salesmanager,
(SELECT sum(s2.amount) FROM sales s2 WHERE
s2.department = s1.department) AS total_for_the_entire_department
FROM sales s1
GROUP BY s1.department, s1.salesmanager
Or just stick your CTE into a subquery FROM
FROM tab1
becomes
FROM (SELECT [...] GROUP BY department, salesmanager) tab1
Given the data, and the absence of any other filters, there shouldn't be any true difference between the two forms though I do not know about about the planner to know whether there is a difference in reality.
