sufian ahmed sufian ahmed - 1 year ago 71
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download