sufian ahmed sufian ahmed - 29 days ago 10
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

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.

Comments