Sum() problems *solved* - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sum() problems *solved*

Author

Topic

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-02-21 : 06:16:39

I have created a SP which sums up my shopping cart total cost.

Looks like this:
CREATE PROCEDURE cart_sum
@sid int
AS
SET NOCOUNT ON
SELECT Sum(p.product_price * c.cart_product_howmany) AS cart_sum
FROM cart c
INNER JOIN products p ON c.cart_product_nr = p.product_id
WHERE cart_session_id = @sid
GO

The problem is that when the cart is empty, the value is not "0" but something else... I cannot do an IF statement in VBscript to change it from "" into "0" either... Is there a way of making it return "0" if the query has no value?

=====================================
Why not try and do the impossible?

Edited by - Swede on 02/21/2002 06:38:26

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-21 : 06:30:53

If there are no rows to sum, it will return Null as a sum. You can use IsNull() to replace this with a zero:

CREATE PROCEDURE cart_sum @sid int AS
SET NOCOUNT ON
SELECT IsNull(Sum(p.product_price * c.cart_product_howmany),0) AS cart_sum
FROM cart c
INNER JOIN products p ON c.cart_product_nr = p.product_id
WHERE cart_session_id = @sid

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-02-21 : 06:33:01

Great, that worked :D

Thanks mate

=====================================
Why not try and do the impossible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources