Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
sumproduct
- Thread starter Ofina
- Start date
SkipVought
Programmer
Hi,
How do you intend to use? Please give a concrete example with sample data from your table(s) and the expected result.
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...
for a NUANCE!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
How do you intend to use? Please give a concrete example with sample data from your table(s) and the expected result.
Skip,
Just traded in my old subtlety...for a NUANCE!
Hi.
I suppose you need the same funcionality as in Excel function SUMPRODUCT(). Here is example, but be aware how this method work with NULL values. It will not include NULL values in your sum result.
If you want you can use ISNULL() function to replace NULL values with your own defined constant. Example:
I suppose you need the same funcionality as in Excel function SUMPRODUCT(). Here is example, but be aware how this method work with NULL values. It will not include NULL values in your sum result.
Code:
declare @mytable table (
col1 int identity not null,
col2 int
)
insert into @mytable values(5)
insert into @mytable values(10)
insert into @mytable values(15)
insert into @mytable values(25)
insert into @mytable values(NULL)
insert into @mytable values(30)
select * from @mytable
select SUM(col1*col2) as sumproduct from @mytableIf you want you can use ISNULL() function to replace NULL values with your own defined constant. Example:
Code:
select SUM(col1*ISNULL(col2,1)) as sumproduct from @mytable- Status