SUM(TestFee) AS TestFee ,
COUNT(*) "No of Tests"
WHERE ReqDate BETWEEN '2016-11-04' AND '2016-11-05'
GROUP BY TestName
One option here is to move the logic of the
WHERE clause into the
SELECT clause and use conditional aggregation:
SELECT TestName, SUM(CASE WHEN ReqDate BETWEEN '2016-11-04' AND '2016-11-05' THEN TestFee ELSE 0 END) AS TestFee, SUM(CASE WHEN ReqDate BETWEEN '2016-11-04' AND '2016-11-05' THEN 1 ELSE 0 END) AS "No of Tests in Range", COUNT(*) AS "Total No of Test" FROM ALLPATIENTINFO GROUP BY TestName
This will guarantee that every test name which appears in your original data set will also appear in the result set.