sufian ahmed sufian ahmed - 1 year ago 65
SQL Question

how can i write this sql according to the following criteria

SELECT TestName,
SUM(TestFee) AS TestFee ,
COUNT(*) "No of Tests"
FROM ALLPATIENTINFO
WHERE ReqDate BETWEEN '2016-11-04' AND '2016-11-05'
GROUP BY TestName


Here only shows Testname ,total,and count number accoroding to the date .but i want to show all of the testName whose are saved in data table. if the test name out of this date then it only shows testname and testfee & count no will be zero.

Answer Source

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.