select SUM(all records) except this record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

select SUM(all records) except this record

Author

Topic

maramusaSQL
Starting Member

2 Posts

Posted - 2005-05-27 : 02:11:00

basically its just a sql problem,

select SUM(all records) except this record

so if have 5 records, the result for record 1 will be sum(2,3,4,5)..etc

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-27 : 02:31:40

Could You please post some more info, so we understand the problem why You would want to do this:
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Technically it's pretty straightforward, just sum the records that You want:
select sum(...) from tbl where ... <> ...

But I suspect there is more to this.

rockmoose

maramusaSQL
Starting Member

2 Posts

Posted - 2005-05-27 : 04:16:13

sorry

I have a sports event with many competitors.
each competitor has a price which you can bet any amount on.
To show the risk(how much we win as bookie) for each competitor, I need to:
Sum(price for this competitor * bet amount for all bets on every other competitor)) -
Sum(price for this competitor * bet amount for all bets on this competitor)
For each competitor

so I can do

table Bets has event, competitor , bet amount, price

select sum(price * bet amount)
from table bets
where event = 999

but i cant do

select sum(price * bet amount for every other competitor) -
sum(price * bet amount for this competitor)
from table bets
where event = 999

would give

event competitor risk
999 2 45
999 3 56
999 4 12
999 5 -7
999 6 5

etc

thanks

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-27 : 05:13:55

SELECT (select sum(price*betamount) from table bets b where b.event=event and b.competitor <> competitor)
as EveryOtherCompetitor,
(select sum(price*betamount) from table bets b where b.event=event and b.competitor = competitor)
as ThisCompetitor
FROM table bets

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-27 : 05:15:25

this would give you all.... better but if you want for 999 only just add a where condition
SELECT (select sum(price*betamount) from table bets b where b.event=event and b.competitor <> competitor)
as EveryOtherCompetitor,
(select sum(price*betamount) from table bets b where b.event=event and b.competitor = competitor)
as ThisCompetitor
FROM table bets WHERE event = 999

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources