Re: 2nd attempt: Window function SQL - can't quite figure it. - Mailing list pgsql-novice

Hi (again) all,



Apologies for this second attempt at my question - obviously
some snafu with my email - maybe Window functions aren't the
only thing I have trouble figuring out! :-)

There **is** a question this time!

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), I tried using this line also...
       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 - same for the 420 of
Eve Nicholas.


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