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

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

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

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.

--

--

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

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



> David J.



--

--

Pál Teleki