Re: 2nd attempt: Window function SQL - can't quite figure it.
| От | David G. Johnston |
|---|---|
| Тема | Re: 2nd attempt: Window function SQL - can't quite figure it. |
| Дата | |
| Msg-id | CAKFQuwY+w2H028pw9ajghG+4AjP5emkz345gV29Zjmi9Z5tYeg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: 2nd attempt: Window function SQL - can't quite figure it. (Pál Teleki <ellenallhatatlan@gmail.com>) |
| Ответы |
Re: 2nd attempt: Window function SQL - can't quite figure it.
|
| Список | pgsql-novice |
SELECT department,
amount,
salesmanager,
-- DISTINCT(salesmanager), I tried using this line also...
SUM(amount) OVER(PARTITION BY department, salesmanager) AS "Sum
by dept.",
ROW_NUMBER() OVER() AS "row number",
ROUND((amount/SUM(amount) OVER(PARTITION BY department,
salesmanager)) * 100) AS "% of total"
FROM sales
-- GROUP BY department, salesmanager
ORDER BY department, salesmanager
Not Test.
You just want these rows returned, right?
SELECT department, salesmanager
FROM sales
GROUP BY department, salesmanager
Along with some calculations:
SELECT *, total_mgr_dept / total_dept_only AS manager_share_of_department
FROM (
SELECT department, salesmanager,
sum(amount)::numeric AS total_mgr_dept,
(sum( sum(amount) ) OVER (PARTITION BY department))::numeric AS total_dept_only,
FROM sales
GROUP BY department, salesmanager
) group_sums
As I said in my other reply writing:
sum(amount) OVER ([...])
will not work.
David J.
В списке pgsql-novice по дате отправления: