multiple count on derived table

Question

I am trying to count total shortlisted and total interested by using a derived table but it's not working.

select  (SELECT COUNT(ID) FROM Dtable WHERE Is_shortlisted=1) AS TOTALSHORLISTED,           
        (SELECT COUNT(ID) FROM Dtable WHERE Is_Interested=1) AS TOTALINERESTED 
from (
        SELECT  BM.ID,
                BM.Is_Interested,
                BM.Is_shortlisted,
                BM.Business_Masla_Status_ID
        FROM  Business_Maslahal BM
        INNER join Vw_MaslaInfo MI 
            on BM.[MaslaHal_ID]=MI.ID and BM.ID=2 AND MI.Masla_status_ID IN(1,2) 
) Dtable

Answer 1

Actually, you don't even need the derived table:

SELECT  SUM(CASE WHEN Is_shortlisted=1 THEN 1 ELSE 0 END) AS TOTALSHORLISTED,
        SUM(CASE WHEN Is_Interested=1 THEN 1 ELSE 0 END) AS TOTALINERESTED 
FROM  Business_Maslahal BM
INNER join Vw_MaslaInfo MI 
ON BM.[MaslaHal_ID]=MI.ID 
WHERE and BM.ID=2 
AND MI.Masla_status_ID IN(1,2)

I've also moved some of the conditions from the ON clause to the WHERE clause. It should have no effect on the results, but it makes the query more readable.

Answer 2

Try this way:

select  SUM(CASE WHEN Is_shortlisted=1 THEN 1 ELSE 0 END) AS TOTALSHORLISTED,
        SUM(CASE WHEN Is_Interested=1 THEN 1 ELSE 0 END) AS TOTALINERESTED 
from (
    SELECT  BM.ID,
            BM.Is_Interested,
            BM.Is_shortlisted,
            BM.Business_Masla_Status_ID
    FROM  Business_Maslahal BM
    INNER join Vw_MaslaInfo MI 
        on BM.[MaslaHal_ID]=MI.ID and BM.ID=2 AND MI.Masla_status_ID IN(1,2) 
) Dtable