CODEWITHSUNDEEP
sqlsql-server-2008
user1263981
user1263981

Reputation: 3147

Return 0 when there is no row returned

I have this query which returns Null, to show 0 i can use ISNULL on outer query around CAST, i don't know if it is better to use ISNULL in the inner query.

I have tried using ISNULL with inner query but it returns no rows instead of showing 0. I have tried removing group by clause but still same results. 

SELECT CAST((SUM(q.AssingWithPO * 1.0) / SUM(q.TotalAssign * 1.0)) * 100
                                                                 AS NUMERIC (10,2))
FROM
(
  SELECT COUNT(DISTINCT a.AssignmentID) AS TotalAssign
    ,(SELECT COUNT(DISTINCT a2.AssignmentID)FROM Assignments a2
       WHERE a2.PODeliveryDate <> '19001212'
        AND a2.AssignmentID = a.AssignmentID ) AS AssingWithPO

  FROM Assignments a
  WHERE a.StaffID = 59
  AND (a.CreatedDate BETWEEN '20130101' AND '20141231')
  GROUP BY a.AssignmentID
)q;

ADDED

I have simplified this query, thanks to @Gordon

SELECT SUM(case when a.PODeliveryDate <> '19001212' then 1.0 else 0.0 end) / COUNT(*)) * 100 as AssignWithPO
 FROM Assignments a
 WHERE a.StaffID = 59 AND
       a.CreatedDate BETWEEN '20130101' AND '20141231';

Now would it be okay to use ISNULL like that?

ISNULL((SUM(case when a.PODeliveryDate <> '19001212' then 1.0 else 0.0 end) / COUNT(*)) * 100 as AssignWithPO,0)

Execution Plan of both queries

enter image description here

Upvotes: 1

Views: 303

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You don't need the second level of subqueries. You can use conditional aggregation instead. I think the following will do what you want:

SELECT CAST((SUM(a.AssignWithPO * 1.0) / SUM(a.TotalAssign * 1.0)) * 100 as NUMERIC (10,2))
FROM (SELECT COUNT(*) AS TotalAssign,
             SUM(case when a.PODeliveryDate <> '19001212' then 1 else 0 end) as AssignWithPO
     FROM Assignments a
     WHERE a.StaffID = 59 AND
           a.CreatedDate BETWEEN '20130101' AND '20141231'
     GROUP BY a.AssignmentID
    ) a;

I'm not 100% sure, because I don't understand the relationships between AssignmentId, StaffId, and CreatedDate, but my assumption is that the rows counted for AssignWithPO are subject to the same conditions as the TotalCount.

You don't need the count(distinct) because AssignmentId is necessarily unique because of the group by. Assuming there is no overlap between the values, you don't need the group by either, nor the outer query:

     SELECT COUNT(*) AS TotalAssign,
            SUM(case when a.PODeliveryDate <> '19001212' then 1.0 else 0.0 end) / COUNT(*) as AssignWithPO
     FROM Assignments a
     WHERE a.StaffID = 59 AND
           a.CreatedDate BETWEEN '20130101' AND '20141231';

Upvotes: 1

Related Questions