in clause + count function

[bhakti_thakkar]

hello,
in a table i store the status of some project (eg. certified = 2 , contracted = 7 , resigned = 1 etc ) now i want to find out count of projects of all the countries with status in (2 , 7). below is my query:



SELECT count(ps.project_id) as ProjectCnt, isnull(psc.CertificationStatus_ID, 0) , isnull(dbo.uf_parmsel_countryname_of_country(p.MainCountry_ID, 1) , 0) AS Country_VC FROM dbo.Project_Subprogram_CertificationStatus_T psc , project_t p , project_subprogram_t ps
WHERE
(psc.Subprogram_ID = '1')
AND (psc.CertificationStatusPerDate_DT <= '2008-01-01')
and p.Project_id = psc.Project_id
and p.Project_id = ps.Project_id
and p.deleted_dt is null
and ps.deleted_dt is null
and ps.resigned_dt is null
and ps.resigned_dt is null
and ps.resigned_dt is null
and p.Maincountry_id is not null
and (psc.CertificationStatus_ID in( 2 ,7 ) or psc.CertificationStatus_ID is null)

group by psc.CertificationStatus_ID , p.MainCountry_ID order by maincountry_id

and below is my output:



ProjectCnt | Status | Country_VC
1 7 GERMANY
6 2 SWITZERLAND
6 7 SWITZERLAND
114 7 TURKEY
218 2 TURKEY
6 2 POLAND
10 7 SOUTH AFRICA
89 2 SOUTH AFRICA
14 2 UNITED STATES
2 7 UNITED STATES



but i want if any of the values in clause is not found then it should return count as 0. in case of the above eg for GERMANY a row should come as :
0 , 2 , GERMANY
also for POLAND:
0 , 7 , POLAND and so on.

how can i get this. i also tried joins but i thing there is somthing to do with in clause..????
Please help

Thanks and brdgs