Thread: Window function SQL - can't quite figure it.

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