Re: "Sum distinct"

From: VC <boston103_at_hotmail.com>
Date: Tue, 14 Dec 2004 00:23:55 GMT
Message-ID: <voqvd.652561$mD.223965@attbi_s02>

Hi,

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:dPydnZenc9pluiPcRVn-gQ_at_giganews.com...
> Here's one method. SQL92 except for the reserved word "value" but tested
> on SQL Server 2000.
>
> SELECT M.manager, M.salary, S.value
> FROM
> (SELECT manager, SUM(salary) AS salary
> FROM salesman
> GROUP BY manager) AS M,
> (SELECT manager, SUM(value) AS value
> FROM salesman, sale
> WHERE salesman.id = sale.salesman_id
> GROUP BY manager) AS S
> WHERE S.manager = M.manager ;
>
> Hope this helps.
>
> --
> David Portas
> SQL Server MVP
> --
>
>

It can be done much simpler and a bit faster (sale need not be scanned twice):

select manager, sum(salary), sum(value)
from salesman,
(select salesman_id, sum(value) value
from sale
group by salesman_id
) sales
where sales.salesman_id=salesman.id
group by manager

VC Received on Mon Dec 13 2004 - 18:23:55 CST