From: | Pál Teleki <ellenallhatatlan(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-novice novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Window function - assistance appreicated. Can't figure it out. |
Date: | 2016-07-18 03:18:58 |
Message-ID: | CAMLfE0No9Bu9qDic2_7iFYhLjNd97ZKAMUgEht30774N8uba1g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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);
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-07-18 12:20:15 | Re: Window function SQL - can't quite figure it. |
Previous Message | Pál Teleki | 2016-07-18 02:48:39 | Window function SQL - can't quite figure it. |