CODEWITHSUNDEEP
sql
Mohammad Imran
Mohammad Imran

Reputation: 37

Sum two tables value and show value if one column is null

I have two table's column and i want to sum both values but my one column is null and when i run below query its showing all null.

SELECT
    Product.ProductID, 
    Product.ProductName, 
    (select round(sum(quantity),18, 2)from [ProductPur] where [ProductPur].[ProductID] = product.ProductID) AS Purchased_quantity,
    (select round(sum(quantity),18 ,2)from [PGDN] where [PGDN].[ProductID] = product.ProductID)+(select round(sum(quantity),18 ,2)from [returnnonreturndetails] where [returnnonreturndetails].[ProductID] = product.ProductID) AS Sold_quantity,
     (select round(sum(quantity),18 ,2)from [ProductPur] where [ProductPur].[ProductID] = product.ProductID) - (select round(sum(quantity),18, 2)from [PGDN] where [PGDN].[ProductID] = product.ProductID) AS Stock
FROM Product
ORDER BY Product.ProductName;

Upvotes: -1

Views: 209

Answers (3)

waka
waka

Reputation: 3407

Depending on your DBMS, you can have to use IFNULL() (MySQL), ISNULL() (SQL Server, MS Access) or NVL() (Oracle). You can also use COALESCE(), which would be the generic solution.

All of those functions are used the same:

ISNULL($statement,$value_if_statement_is_null)

For example 

select round(sum(isnull(quantity,0)),18, 2)
from [ProductPur]
where [ProductPur].[ProductID] = product.ProductID)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The generic SQL solution uses COALESCE():

SELECT p.ProductID, p.ProductName, 
       (select round(sum(quantity),18, 2) from ProductPur pp where pp.[ProductID] = p.ProductID) AS Purchased_quantity,
       (COALESCE( (select round(sum(quantity), 18, 2) from [PGDN] pg where pg.[ProductID] = p.ProductID), 0) +
        COALESCE( (select round(sum(quantity), 18, 2) from returnnonreturndetails rd where rd.[ProductID] = p.ProductID), 0)
       ) AS Sold_quantity,
       (COALESCE( (select round(sum(quantity), 18, 2) from ProductPur pp where pp.[ProductID] = p.ProductID), 0) -
        COALESCE( (select round(sum(quantity), 18, 2) from [PGDN] pg where pg.[ProductID] = p.ProductID), 0)
       ) AS Stock
FROM Product p
ORDER BY p.ProductName;

In SQL Server, ISNULL() is a better choice, for performance reasons.

Upvotes: 1

programtreasures
programtreasures

Reputation: 4298

you need to check value with isnull function i.e. isnull(quantity,0)

Upvotes: 1

Related Questions