sql - Using COUNT Function in Table Joins -

i writing sql code report page that joins 3 tables. here query have written.

comm.commandtext = "select count(distinct courses.courseid) coursecount, count(distinct students.studentid) studentcount, count(students.startdate) startcount, school.name, school.startdate, school.schoolfees " +                 "from schools " +                 "left join courses on (school.schoolid = courses.schoolid) " +                 "left join students on (school.schoolid = student.schoolid) " +                 "where school.active = 1 " +                 "group school.name, school.startdate, school.schoolfees"; 

the above query works well. want show count of student.startdate each school student.startdate satisfy condition. here query want use

select count(students.startdate) students student.startdate >= dateadd(month, -1, getdate()); 

i want above query return part of main query dont know how achieve it. appreciated. thanks

when want aggregates different tables, should not join tables , aggregate, build aggregates first , join these instead. in case able avoid issues counting distinct ids, not possible (i.e. when looking sums or avarages). can count conditionally case when.

select    coalesce(c.coursecount, 0) coursecount,   coalesce(s.studentcount, 0) studentcount,   coalesce(s.startcount, 0) startcount,   school.name,    school.startdate,    school.schoolfees schools left join (   select schoolid, count(*) coursecount   courses    group schoolid ) c on c.schoolid = school.schoolid left join (   select      schoolid,      count(*) studentcount,     count(case when startdate >= dateadd(month, -1, getdate() 1 end) startcount   students    group schoolid ) s on s.schoolid = school.schoolid school.active = 1; 

in case guaranteed every school have @ least 1 student , 1 course (which case), can change outer joins inner joins , rid of coalesce expressions.