views:

690

answers:

4

How can I get a default value of 0 if a sum does not return any rows?

Edit: I have edited this post to add a more thorough example (which the previous one didn't encounter)

E.g.

 DECLARE @Item TABLE
(
    Id int,
    Price decimal,
    PricePer decimal
)

DECLARE @OrderItem TABLE
(
    Id int,
    ItemId int,
    ChargedPrice nvarchar(10),
    QtyRequired int,
    QtyLeftToDespatch int
)

INSERT INTO @Item (Id,Price,PricePer) VALUES (1,1.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (2,2.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (3,3.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (4,4.00, 1)

INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 1,1,100,100,50 )
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 2,1,200,300,50)
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 3,1,300,300,50 )

DECLARE @ItemIdTest int 
SET @ItemIdTest = 4

SELECT SUM((price/priceper)*QtyRequired) as total_value,
       SUM((price/priceper)*QtyRequired) as outstanding_value
FROM @Item i
INNER JOIN @OrderItem o ON i.Id = o.ItemId
WHERE i.Id = @ItemIdTest
group by itemId

This will return

total_value, outstanding_value
==============
<No rows>

But I want it to have 0 as the default value returned. However, if you do a SELECT to select it, then a second SELECT to select a default, 2 result sets will be returned.

Can I do it just in one? I have looked at COALESCE, but this only works if NULL is returned, which it isn't.

Edit: I think the problem is to do with the group with, but is there a reason why still it doesn't come back with any results?

+7  A: 

Try this

SELECT ISNULL(SUM(Debt) ,0)
FROM SupplierDebt
WHERE Id = @TestId
astander
+1 Beat me to it.
NYSystemsAnalyst
+2  A: 

SQL 2k5: this worked for me:

isnull(sum(Dept), 0)
n8wrl
+3  A: 

Or use

SELECT COALESCE(SUM(Debt) ,0)
FROM SupplierDebt
WHERE Id = @TestId

COALESCE is an ANSI standard, if that bothers you and can take multiple parameters.

bleeeah
+1  A: 
 DECLARE @Item TABLE
(
    Id int,
    Price decimal,
    PricePer decimal
)

DECLARE @OrderItem TABLE
(
    Id int,
    ItemId int,
    ChargedPrice nvarchar(10),
    QtyRequired int,
    QtyLeftToDespatch int
)

INSERT INTO @Item (Id,Price,PricePer) VALUES (1,1.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (2,2.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (3,3.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (4,4.00, 1)

INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 1,1,100,100,50 )
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 2,1,200,300,50)
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 3,1,300,300,50 )

DECLARE @ItemIdTest int 
SET @ItemIdTest = 4

SELECT COALESCE(SUM((price/priceper)*QtyRequired),0) as total_value,
       COALESCE(SUM((price/priceper)*QtyRequired),0) as outstanding_value
FROM @Item i
LEFT OUTER JOIN @OrderItem o ON i.Id = o.ItemId
WHERE i.Id = @ItemIdTest
group by itemId
bleeeah
when you e.g. SET @ItemIdTest = 5 (non existing itemId) it will return empty rows
kristof
I think he needs to re-think the design of his app!
bleeeah
+1 actually looking again at your solution it seems that it should work for this problem
kristof
I would not make sense to expect 0 when asking about itemId that does not exist at all like 5 ( no rows returned then)as opposed to ItemId not present in any orders (0 returned)
kristof
Why is it a design problem to get the totals? Surely it shows we are considering all possibilities?I've over-simplified it here. The reason for this query is to find out how much money is to be owed to a supplier substituted with item in here). But the idea is to give the query a supplier ID, and the query returns the amount of money outstanding for this supplier (instead of the item).Doesn't seem like a design flaw to me?
Dominic Zukiewicz
Nope your right. I stand corrected!
bleeeah