sql server programming: Eliminate duplicates from sum, not display -- sql server programming

Re: Eliminate duplicates from sum, not display

xyb
11/30/2006 5:33:58 PM

sql server programming:

something like this:?
select personid,personname,sum(case servicetype =3D ''product' then
profit else 0.00 end) product,sum(case servicetype =3D ''service' then
profit else 0.00 end) service,
sum(profit) total
from tables with join
:)
"ana9 via SQLMonster.com =D0=B4=B5=C0=A3=BA
"
[quoted text, click to view]

Eliminate duplicates from sum, not display

ana9 via SQLMonster.com
11/30/2006 10:21:54 PM

I run a query for the salesperson, company name, project name, service type
for the project and profit. I have to group the results based on the service
type. I would like to be able to sum the profits for each salesperson
however if a project has two different service types(product and service) it
will get counted twice in the sum profit.

e.g.

Tom Thumb
Product:
ABCco $5000
XYZ $4200
Service:
XYZ $4200
Total: $13400

The total should be $9200, but the XYZ is counted twice because of the two
different service types.

Is there any way to eliminate the duplicates in either the report or the
query without using stored procedures?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200611/1

Re: Eliminate duplicates from sum, not display

ana9 via SQLMonster.com
12/1/2006 1:55:52 PM

so simple. I was trying to make it way more complicated than necessary,
thanks for bringing me back to earth.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200612/1

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.