How to retrieve count of records in SELECT statement

I am trying to retrieve the right count of records to mitigate an issue I am having. The below query returns 327 records from my database:

 SELECT DISTINCT COUNT(at.someid) AS CountOfStudentsInTable FROM tblJobSkillAssessment AS at 
                INNER JOIN tblJobSkills j ON j.jobskillid = at.skillid
                LEFT JOIN tblStudentPersonal sp ON sp.someid2 = at.someid
                INNER JOIN tblStudentSchool ss ON ss.monsterid = at.someid
                INNER JOIN tblSchools s ON s.schoolid = ss.schoolid
                INNER JOIN tblSchoolDistricts sd ON sd.schoolid = s.schoolid
                INNER JOIN tblDistricts d ON d.districtid = sd.districtid
                INNER JOIN tblCountySchools cs ON cs.schoolid = s.schoolid
                INNER JOIN tblCounties cty ON cty.countyid = cs.countyid
                INNER JOIN tblRegionUserRegionGroups rurg ON rurg.districtid = d.districtid
                INNER JOIN tblGroups g ON g.groupid = rurg.groupid
                WHERE ss.graduationyear IN (SELECT Items FROM FN_Split(@gradyears, ',')) AND sp.optin = 'Yes' AND g.groupname = @groupname

Where I run into trouble is trying to reconcile that with the below query. One is for showing just a count of all the particular students the other is showing pertinent information for a set of students as needed but the total needs to be the same and it is not. The below query return 333 students – the reason is because the school the student goes to is in two separate counties and it counts that student twice. I can’t figure out how to fix this.

 SELECT DISTINCT @TableName AS TableName, d.district AS LocationName, cty.county AS County, COUNT(DISTINCT cc.monsterid) AS CountOfStudents, d.IRN AS IRN FROM tblJobSkillAssessment AS cc
            INNER JOIN tblJobSkills AS c ON c.jobskillid = cc.skillid
            INNER JOIN tblStudentPersonal sp ON sp.monsterid = cc.monsterid
            INNER JOIN tblStudentSchool ss ON ss.monsterid = cc.monsterid
            INNER JOIN tblSchools s ON s.schoolid = ss.schoolid
            INNER JOIN tblSchoolDistricts sd ON sd.schoolid = s.schoolid
            INNER JOIN tblDistricts d ON d.districtid = sd.districtid
            INNER JOIN tblCountySchools cs ON cs.schoolid = s.schoolid
            INNER JOIN tblCounties cty ON cty.countyid = cs.countyid
            INNER JOIN tblRegionUserRegionGroups rurg ON rurg.districtid = d.districtid
            INNER JOIN tblGroups g ON g.groupid = rurg.groupid
            WHERE ss.graduationyear IN (SELECT Items FROM FN_Split(@gradyears, ',')) AND sp.optin = 'Yes' AND g.groupname = @groupname
            GROUP BY cty.county, d.IRN, d.district
            ORDER BY LocationName ASC

Answer

If you just want the count, then perhaps count(distinct) will solve the problem:

select count(distinct at.someid)

I don’t see what at.someid refers to, so perhaps:

select count(distinct cc.monsterid)

Advertisement

Answer