Simple SUM - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Simple SUM

Author

Topic

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-04-05 : 07:25:49

Hi,

How do I sum all of the returned values into my output param ? This returns multiple rows, all data is oftype decimal.

Thanks
Bob

ALTER proc
spPSICalcA9

@iReturn int output,
@Contract varchar (8)

as

Select
sd.HoursLostRain,
sd.HoursLostMaxT,
sd.HoursLostMinT,
sd.HoursLostFrost,
sd.HoursLostWind,
sd.HoursLostVis

from
SiteDiary sd
where
sd.Contract = @Contract

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-05 : 07:29:08

Do you really want to only return 1 value?


SELECT SUM(Select 
sum(sd.HoursLostRain + sd.HoursLostMaxT + sd.HoursLostMinT + sd.HoursLostFrost + sd.HoursLostWind + 
sd.HoursLostVis)
from
SiteDiary sd
where
sd.Contract = @Contract) as value

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-04-05 : 07:34:33

Yes, I do only want to return a single value, that should be a sum of all of those fields for all the rows returned for that @Contract

I have tried the above, but am getting errors. This is what I have....

ALTER proc
spPSICalcA9

@iReturn int output,
@Contract varchar (8)

as

SELECT SUM
(Select sum(sd.HoursLostRain + sd.HoursLostMaxT + sd.HoursLostMinT + sd.HoursLostFrost + sd.HoursLostWind + sd.HoursLostVis)
from
SiteDiary sd
where
sd.Contract = @Contract)

as value

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-04-05 : 07:36:42

Fixed it. Sorry.

Select
@iReturn = sum(sd.HoursLostRain + sd.HoursLostMaxT + sd.HoursLostMinT + sd.HoursLostFrost + sd.HoursLostWind + sd.HoursLostVis)
from
SiteDiary sd
where
sd.Contract = @Contract

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 07:40:50

Try this

create proc spPSICalcA9
@iReturn int output,
@Contract varchar (8)
as
BEGIN
    SELECT @iReturn = SUM(sd.HoursLostRain + sd.HoursLostMaxT + sd.HoursLostMinT + sd.HoursLostFrost + sd.HoursLostWind + sd.HoursLostVis)
    FROM   SiteDiary sd
    where  d.Contract = @Contract
END

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-05 : 07:59:00

quote:
Originally posted by staticbob

Fixed it. Sorry.

Select
@iReturn = sum(sd.HoursLostRain + sd.HoursLostMaxT + sd.HoursLostMinT + sd.HoursLostFrost + sd.HoursLostWind + sd.HoursLostVis)
from
SiteDiary sd
where
sd.Contract = @Contract

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

Yeah, sorry, answering before my brain was fully engaged..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources