SQL query dont show Subtrated values which are 0

zolf
zolf used Ask the Experts™
on
Hello there,

I have this select query which subtracts two columns and then I have a SUm on them. It is working as I am expecting but can I refine my result so that the value 0 is not included in the result

SELECT
    dbo.Branch.code,
     dbo.Branch.name        AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity
    ) AS AvailQty

Open in new window


cheers
ZOlf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
try:

SELECT
    dbo.Branch.code,
     dbo.Branch.name        AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity
    ) AS AvailQty

from ....

group by  dbo.Branch.code,
     dbo.Branch.name
Having AvailQty > 0
Mike EghtebasDatabase and Application Developer

Commented:
Select code, Branch, AvailQty
From (
SELECT code
            , name AS Branch
            , SUM(actualQuantity - reservedQuantity ) AS AvailQty
From dbo.Branch) As D (code, Branch, AvailQty)
Where AvailQty > 0

Open in new window

Ryan ChongSoftware Team Lead
Commented:
>> can I refine my result so that the value 0 is not included in the result
or to be "exact"...

group by  dbo.Branch.code,
     dbo.Branch.name
Having AvailQty <> 0
Mike EghtebasDatabase and Application Developer
Commented:
correction:
Select code, Branch, AvailQty
From (
SELECT code
     , name AS Branch
     , SUM(actualQuantity - reservedQuantity ) AS AvailQty
From dbo.Branch
Group By code, name ) As D (code, Branch, AvailQty)
Where AvailQty > 0 -- or Where AvailQty <> 0 as required

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial