Re: [NOVICE] 2nd attempt: Window function SQL - can't quite figure it.

[Pál Teleki]

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


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

[David G. Johnston]

On Mon, Jul 18, 2016 at 2:29 PM, Pál Teleki <ellenall...@gmail.com> wrote:

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

​Not Test.

​

You just want these rows returned, right?​

SELECT department, salesmanager

FROM sales

GROUP BY department, salesmanager

​Along with some calculations:

SELECT *, total_mgr_dept / total_dept_only AS manager_share_of_department

FROM (

SELECT department, salesmanager,

sum(amount)::numeric AS total_mgr_dept,

(sum(  sum(amount)  ) OVER (PARTITION BY department))::numeric AS total_dept_only,

FROM sales

GROUP BY department, salesmanager

​) group_sums

As I said in my other reply writing:

sum(amount) OVER ([...]) 

will not work.

David J.

[Pál Teleki]

Thanks for that - I managed

> SELECT *, total_mgr_dept / total_dept_only AS manager_share_of_department

> <rest of query snipped...>

I managed to do using two CTEs as shown below.

I was wondering, how would it be possible to do what I want
using traditional SQL? That is, no CTE's and no Windowing
functions?

====== My effort using two CTE's ====

WITH tab1 AS (
SELECT department, salesmanager, SUM(amount) as sm1 FROM sales
GROUP BY department, salesmanager
),
tab2 AS (
SELECT department, SUM(amount) as sm2 FROM sales
GROUP BY department
)
SELECT tab1.*, tab2.sm2, ROUND((tab1.sm1/tab2.sm2) * 100, 2) AS "%age
of Dept. income"
FROM tab1
INNER JOIN tab2 ON
tab1.department = tab2.department;

> David J.


Pál


--

Pál Teleki

[David G. Johnston]

On Mon, Jul 18, 2016 at 3:24 PM, Pál Teleki <ellenall...@gmail.com> wrote:

Thanks for that - I managed

> SELECT *, total_mgr_dept / total_dept_only AS manager_share_of_department
> <rest of query snipped...>

I managed to do using two CTEs as shown below.

I was wondering, how would it be possible to do what I want
using traditional SQL? That is, no CTE's and no Windowing
functions?

====== My effort using two CTE's ====

WITH tab1 AS (
  SELECT department, salesmanager, SUM(amount) as sm1 FROM sales
  GROUP BY department, salesmanager
),
tab2 AS (
  SELECT department, SUM(amount) as sm2 FROM sales
  GROUP BY department
)
SELECT tab1.*, tab2.sm2, ROUND((tab1.sm1/tab2.sm2) * 100, 2) AS  "%age
of Dept. income"
FROM tab1
INNER JOIN tab2 ON
tab1.department = tab2.department;

​Correlated subqueries.​

​Not tested, should at least give an idea even if it has an error​

​SELECT s1.department, s1.salesmanager,

(SELECT sum(s2.amount) FROM sales s2 WHERE​

 

​s2.department = s1.department) AS total_for_the_entire_department

FROM sales s1

GROUP BY s1.department, s1.salesmanager​

Or just stick your CTE into a subquery FROM

​FROM tab1

becomes

FROM (SELECT [...] GROUP BY department, salesmanager) tab1​

Given the data, and the absence of any other filters, there shouldn't be any true difference between the two forms though I do not know about about the planner to know whether there is a difference in reality.

David J.

[Pál Teleki]

Thanks again - that works nicely.

One final question follows on from my original question.

This query (using "traditional" SQL) works:

SELECT t1.dep, t1.man,
t1.deptot,
t1.stot_per_man,
ROUND((t1.stot_per_man/t1.deptot * 100), 2) AS "%age sales per manager"
FROM
(
SELECT s1.department AS dep, s1.salesmanager AS man,

(SELECT sum(s2.amount) FROM sales s2 WHERE s2.department =

s1.department) AS deptot,
(SELECT sum(s3.amount) FROM sales s3 WHERE s3.salesmanager =
s1.salesmanager) AS stot_per_man
-- stot_per_man/deptot AS "%age sales per manager"

FROM sales s1
GROUP BY s1.department, s1.salesmanager

) AS t1


Note the commented line -- stot_per_man/deptot AS "%age sales per manager"

If I uncomment it, I get "ERROR: column "stot_per_man" does not exist"

Now, why can't I use deptot and stot_per_man aliases in my query? It would
greatly simplify the SQL by removing the need for a subquery (or inline view
as Oracle calls them).

Thanks again,


Pál.



> David J.

--

[David G. Johnston]

On Monday, July 18, 2016, Pál Teleki <ellenall...@gmail.com> wrote:

Now, why can't I use deptot and stot_per_man aliases in  my query? It would
greatly simplify the SQL by removing the need for a subquery (or inline view
as Oracle calls them).

SQL execution of a query is only done once.  Referring to an alias at the same level in a query would require some kind of duplication to work.  Either two phase processing or cloning the subselect that is being alised and executing it a second time to be used in the second location.  Neither are desirable.

David J.

[Pál Teleki]

>> Now, why can't I use deptot and stot_per_man aliases in my query? It
>> would greatly simplify the SQL by removing the need for a subquery (or inline
>> view as Oracle calls them).


> SQL execution of a query is only done once. Referring to an alias at the
> same level in a query would require some kind of duplication to work.
> Either two phase processing or cloning the subselect that is being alised
> and executing it a second time to be used in the second location. Neither
> are desirable.

Thanks for the assistance and clarification. Appreciate your time!


Pál