count (?)

  • Thread starter Thread starter Stephen Remde
  • Start date Start date
S

Stephen Remde

can anyone see where im going wrong with this...

select stud_company, count(*) as c1,
count(enroll_course=1 and enroll_status='P') as c2,
count(enroll_course=2 and enroll_status='P') as c3,
count(enroll_status='F') as c4
from enrollments, students
where enroll_student=stud_num
group by stud_company

i want to view results by company:

Results by company
Company Enrolments Level 1 Passes Level 2 Passes Fails
Acme 10 7 2
1
Microsoft 5 2 3
0

Thanks
Stephen
 
Stephen Remde said:
can anyone see where im going wrong with this...

select stud_company, count(*) as c1,
count(enroll_course=1 and enroll_status='P') as c2,
count(enroll_course=2 and enroll_status='P') as c3,
count(enroll_status='F') as c4
from enrollments, students
where enroll_student=stud_num
group by stud_company

i want to view results by company:

Results by company
Company Enrolments Level 1 Passes Level 2 Passes Fails
Acme 10 7
2 1
Microsoft 5 2
3 0

Thanks
Stephen
Click to expand...

For Jet SQL, I think you need something like this (off the top of my
head):

select
stud_company,
count(*) as c1,
Sum(IIf(enroll_course=1 and enroll_status='P', 1, 0)) as c2,
Sum(IIf(enroll_course=2 and enroll_status='P', 1, 0)) as c3,
Sum(IIf(enroll_status='F', 1, 0)) as c4
from
enrollments
inner join students
on enroll_student=stud_num
group by
stud_company

You can also do your conditional sums like this:

Abs(Sum(enroll_course=1 and enroll_status='P')) as c2

or even

-Sum(enroll_course=1 and enroll_status='P') as c2

but that depends on the database engine's internal representation of
True and False, which isn't portable.
 
Back
Top