CODEWITHSUNDEEP
sql-serversubquery
Action Author
Action Author

Reputation: 99

SQL Server : return zero all rows if sub-query result IS NULL

I need to return a zero if the subquery returns no rows. I have tried multiple suggestions found here on SO, to no avail, along with a case statement and coalesce.

My end goal is to sum the resulting subquery results together, but since the 'misc' query returns no rows, the TTL field is blank.

Where am I going wrong, and how do I correct?

Select 
    s.STID,
    rent.amt As RENT,
    misc.amt As MISC,
    rent.amt + misc.amt As TTL
From 
    (Select 
         s.STID
     From 
         Stores s
     Where 
         s.STID Not In (7, 999) ) As s
Left Join 
    (Select 
         Income.STID,
         Sum(Case When Income.Amount Is Null Then 0 Else Income.Amount End) As amt
     From 
         Income
     Inner Join 
         IncomeTypes On IncomeTypes.IncTypeID = Income.IncTypeID
     Inner Join 
         IncomeSection On IncomeSection.IncSecID = IncomeTypes.IncSecID
     Where 
         Income.IncomeDate = dbo.getdateparam(92, 999) And
         IncomeTypes.IncTypeID In (1, 2)
     Group By 
         Income.STID) As rent on s.STID = rent.STID
Left Join 
    (Select 
         Income.STID,
         Coalesce(Sum(Income.Amount), 0) As amt
     From 
         Income
     Inner Join 
         IncomeTypes On IncomeTypes.IncTypeID = Income.IncTypeID
     Inner Join 
         IncomeSection On IncomeSection.IncSecID = IncomeTypes.IncSecID
     Where 
         Income.IncomeDate = dbo.getdateparam(92, 999) And
         IncomeTypes.IncTypeID In (20, 21, 22)
     Group By 
         Income.STID) As misc On s.STID = misc.STID
Order By 
    s.STID

Currently returns:

STID    RENT      MISC    TTL
1       1234.56 
2       1234.56 
3       1234.56 
4       1234.56 
5       1234.56

I have spent the last three hours trying to return as below:

STID    RENT      MISC    TTL
1       1234.56   0       1234.56
2       1234.56   0       1234.56 
3       1234.56   0       1234.56 
4       1234.56   0       1234.56 
5       1234.56   0       1234.56

Upvotes: 2

Views: 2281

Answers (1)

Siyual
Siyual

Reputation: 16917

Use COALESCE:

Select  s.STID
      , rent.amt As RENT
      , Coalesce(misc.amt, 0) As MISC
      , rent.amt + Coalesce(misc.amt, 0) As TTL
From    (Select s.STID
         From   Stores s
         Where  s.STID Not In (7, 999)
        ) As s
Left Join (Select   Income.STID
                  , Sum(Case When Income.Amount Is Null Then 0
                             Else Income.Amount
                        End) As amt
           From     Income
           Inner Join IncomeTypes
                    On IncomeTypes.IncTypeID = Income.IncTypeID
           Inner Join IncomeSection
                    On IncomeSection.IncSecID = IncomeTypes.IncSecID
           Where    Income.IncomeDate = dbo.getdateparam(92, 999)
                    And IncomeTypes.IncTypeID In (1, 2)
           Group By Income.STID
          ) As rent
        On s.STID = rent.STID
Left Join (Select   Income.STID
                  , Coalesce(Sum(Income.Amount), 0) As amt
           From     Income
           Inner Join IncomeTypes
                    On IncomeTypes.IncTypeID = Income.IncTypeID
           Inner Join IncomeSection
                    On IncomeSection.IncSecID = IncomeTypes.IncSecID
           Where    Income.IncomeDate = dbo.getdateparam(92, 999)
                    And IncomeTypes.IncTypeID In (20, 21, 22)
           Group By Income.STID
          ) As misc
        On s.STID = misc.STID
Order By s.STID;

COALESCE takes the first non-NULL value in the values provided. If misc.amt is NULL, it would default it to the second value, 0.

Upvotes: 6

Related Questions