Re: [NOVICE] Window function - assistance appreicated. Can't figure it out.

[Pál Teleki]

Hi all,

I'm having trouble figuring out a Window function query (full DML
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 yields


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


The result I want is to "compress" my result futher - the two
pairs of records marked - I wish to appear as 1 pair.

I want the totals of Eve Nicholas and Sam Dakota (data is
fictional) to be taken together and for example, for the
170 total of Sam Dakota to be expressed as a percentage
of the Computers department.


DML and DDL


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


INSERT INTO sales VALUES (1, 'Computers', 'John Dale', 'Notebook', 100);
INSERT INTO sales VALUES (2, 'Computers', 'Sam Dakota', 'Desktop
computer', 100);
INSERT INTO sales VALUES (3, 'Computers', 'Sam Dakota', 'Desktop computer', 70);
INSERT INTO sales VALUES (4, 'Computers', 'Eve Nicolas', 'Pocket PC', 270);
INSERT INTO sales VALUES (5, 'Computers', 'Eve Nicolas', 'Smartphone', 150);
INSERT INTO sales VALUES (6, 'Cars', 'Nick Hardy', 'Mercedes', 300);
INSERT INTO sales VALUES (7, 'Cars', 'James Wilson', 'BMW', 100);
INSERT INTO sales VALUES (8, 'Cars', 'Tom Sawyer', 'Audi', 170);


--
Sent via pgsql-novice mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice