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

On Mon, Jul 18, 2016 at 3:24 PM, Pál Teleki <ellenallhatatlan(at)gmail(dot)com>
wrote:

> 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.

David J.