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)
);