November 19, 2014 at 9:40 am
Hello,
I need to get the Total value by SUM the records of a Table Item.
What would be the quickest way?
- using a computed column on master table and get value by function
or
- make a SUM select with group by on table Item ?
Examples:
-- total is a computed column get by dbo.fu_salesTotal( )
select idSale, total from sales
-- get total by SUM
select S.idSale, sum(I.valueItem) as total
from salesItems I
inner join sales S ON S.idSale = I.idSale
group by S.idSale
-- Function
create function dbo.fu_salesTotal(@idSale int) returns decimal(12,2)
as
begin
declare @value decimal(12,2);
select @value = sum(valueItem)
from salesItems
where idSale = @idSale
return isnull(@value, 0);
end;
go
Thank you!
JoseACJr
November 19, 2014 at 2:20 pm
Better would be an inline-table-valued-function.
If you intend to stick with a scalar function, at least get rid of the variable:
create function dbo.fu_salesTotal(@idSale int)
returns decimal(12,2)
as
begin;
return isnull((
select sum(valueItem)
from salesItems
where idSale = @idSale
), 0);
end;
go
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2014 at 3:53 am
Hello, thank you,
But, is it possible to use a table value function in a computed column?
JoseAcJR
November 20, 2014 at 10:53 am
No, that's true, but you could use it in a view.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply