"Sum distinct"

From: Tom Barnes <nospam1978_at_yahoo.com>
Date: 11 Dec 2004 10:43:38 -0800
Message-ID: <1102790618.670916.107130@z14g2000cwz.googlegroups.com>

Let's say I have a sales organization (denormalized and simplified design):

CREATE TABLE salesman (id NUMBER PRIMARY KEY, manager VARCHAR2(10), salary NUMBER);
CREATE TABLE sale (id NUMBER PRIMARY KEY, salesman_id NUMBER REFERENCES salesman(id), value NUMBER);

INSERT INTO salesman VALUES (1,'Manager1',10);
INSERT INTO salesman VALUES (2,'Manager1',20);
INSERT INTO salesman VALUES (3,'Manager1',10);
INSERT INTO salesman VALUES (4,'Manager2',5);

INSERT INTO sale VALUES (1,1,4);
INSERT INTO sale VALUES (2,1,7);
INSERT INTO sale VALUES (3,2,8);
INSERT INTO sale VALUES (4,3,9);

INSERT INTO sale VALUES (5,4,4);
INSERT INTO sale VALUES (6,4,3);

I want a query that shows for each manager: his salesmen's total salary and the total value of their sales. The following naive query doesn't work since the salesmens' salaries will be counted once for each of their sales:

SELECT manager, SUM(salary), SUM(value)
FROM salesman,sale
WHERE salesman.id = sale.salesman_id
GROUP BY manager;

MANAGER SUM(SALARY) SUM(VALUE)
---------- ----------- ----------

Manager1            50         28
Manager2            10          7

(The salary sums I want is 40 and 5)

The best way I can figure out is to use an inline view with an analytical function, like this:

SELECT manager, SUM(q) salary, SUM(value) FROM
(SELECT manager,

salary/COUNT(*) OVER (PARTITION BY salesman.id) q, value
FROM salesman, sale
WHERE salesman.id = sale.salesman_id
ORDER BY manager, salesman.id)
GROUP BY manager ORDER BY manager;

MANAGER SALARY SUM(VALUE)
---------- ---------- ----------

Manager1           40         28
Manager2            5          7

Doesn't look very elegant though...

1. What's the best way to write a query like this to get a "distinct sum" in Oracle 9ir2?
2. How do I do it without using analytical functions (with subqueries, inline views)?

Thanks a lot! Received on Sat Dec 11 2004 - 12:43:38 CST