Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
Thread: in clause + count function
07-30-2008, 02:18 PM #1
- Join Date
- Sep 2006
- India Mumbai
- Thanked 1 Time in 1 Post
in clause + count function
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
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
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..????
Thanks and brdgs