From: | Pál Teleki <ellenallhatatlan(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-novice novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: 2nd attempt: Window function SQL - can't quite figure it. |
Date: | 2016-07-18 19:24:20 |
Message-ID: | CAMLfE0Pjfze5r-iWo_bZAGMq6ELqVOtpf6QuM-eo84qd9SuEyQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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;
> David J.
Pál
--
Pál Teleki
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-07-18 19:31:52 | Re: 2nd attempt: Window function SQL - can't quite figure it. |
Previous Message | David G. Johnston | 2016-07-18 19:00:33 | Re: 2nd attempt: Window function SQL - can't quite figure it. |