From: | Pál Teleki <ellenallhatatlan(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-novice novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Window function SQL - can't quite figure it. |
Date: | 2016-07-18 02:48:39 |
Message-ID: | CAMLfE0O8Coe+OdhXsw603HXT7=S1qs3o-zvzGSp4NJkn7OKV8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi all,:
I'm struggling to get to grips with Window functions (DDL and DML at
end of post).
I have the following query:
SELECT department,
amount,
salesmanager,
-- DISTINCT(salesmanager),
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
which gives me back the following data:
department|amount|salesmanager|Sum by dept.|row number|% of total
Cars|100|James Wilson|100|1|100
Cars|300|Nick Hardy|300|2|100
Cars|170|Tom Sawyer|170|3|100
Computers|150|Eve Nicolas|420|4|36
Computers|270|Eve Nicolas|420|5|64
Computers|100|John Dale|100|6|100
Computers|70|Sam Dakota|170|7|41
Computers|100|Sam Dakota|170|8|59
CREATE TABLE sales
(
saleid serial,
department character varying(30),
salesmanager character varying(30),
subject character varying(100),
amount numeric,
CONSTRAINT sales_pkey PRIMARY KEY (saleid)
);
From | Date | Subject | |
---|---|---|---|
Next Message | Pál Teleki | 2016-07-18 03:18:58 | Re: Window function - assistance appreicated. Can't figure it out. |
Previous Message | Nigel Straightgrain | 2016-07-13 03:54:42 | How to upgrade from PostgreSQL v9.1.2 to v9.5.3? |