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

score 3 · Accepted Answer · answered Jun 14 '16 at 12:56

3

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.

answered Jun 14 '16 at 12:56

Zohar Peled

79,642
10
69
121

Nice! I was to lazy to rewrite without using sub-query :) – gofr1 Jun 14 '16 at 13:08
Thanks, I think this is more efficient wa. – shahid zaman Jun 16 '16 at 06:55

score 2 · Answer 2 · answered Jun 14 '16 at 12:07

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

multiple count on derived table

2 Answers2