CODEWITHSUNDEEP
sqlms-accesscountif-statement
Qatrelnada
Qatrelnada

Reputation: 45

SQL COUNT command

CREATE TABLE Student_Exam(
  Exam_ID INTEGER,
  S_ID INTEGER,
  Primary Key (Exam_ID, S_ID),
  Foreign Key (Exam_ID) References Exams(Exam_ID),
  Foreign Key (S_ID) References Students(S_ID),
  Pass TEXT
);

CREATE TABLE Students(
  S_ID INTEGER PRIMARY KEY,
  First_Name TEXT,
  Surname TEXT
);

CREATE TABLE Exams(
  Exam_ID INTEGER PRIMARY KEY,
  Date_Taken DATE
);

How can I correct this?

SELECT MAX(Students.S_ID) AS S_ID, **count(Pass="Yes")** AS No_of_Exams_Taken
FROM Student_Exam, Students, Exams
WHERE Students.S_ID=Student_Exam.S_ID And Exams.Exam_ID=Student_Exam.Exam_ID And (Exams.Date_Taken)>=#1/1/2010# And (Exams.Date_Taken)<=#12/31/2010#
GROUP BY Student_Exam.S_ID;

I would like to count the number of exams each student has passed? How is the count should be in the SELECT command?

SELECT Student.S_ID, COUNT(*) AS Final_Exam_Level FROM Student, Exams, Student_Exam WHERE (Student.S_ID)=Student_Exam.S_ID And ((Exams.Exam_ID)=Student_Exam.Exam_ID) And (Exams.Date_Taken)<=#12/31/2010# GROUP BY Student.S_ID, Student.Course_Level ORDER BY Student.S_ID;

Above is the correct query

Upvotes: 1

Views: 353

Answers (2)

dmc
dmc

Reputation: 2684

This version answers the specific question of how to count based on a condition. As a bonus it will also provide the number of exams taken.

select
   s.s_id as student_id
  ,count(case when se.pass='Yes' then 1 end) as exams_passed
  ,count(*) as exams_taken
from
   Students s
   join Student_Exam se on s.s_id = se.s_id
   join Exam e on se.exam_id = e.exam_id
where
   e.date_taken between #1/1/2010# and #12/31/2010#
group by
   s.s_id

Based on your other comments, a version avoiding the join would be:

select
   s.s_id as student_id
  ,count(case when se.pass='Yes' then 1 end) as exams_passed
  ,count(*) as exams_taken
from
   Students s
  ,Student_Exam se
  ,Exam e
where
   s.s_id = se.s_id
   and se.exam_id = e.exam_id
   and e.date_taken between #1/1/2010# and #12/31/2010#
group by
   s.s_id

Or, here's a simpler version that doesn't get so fancy with case:

select
   s.s_id as student_id
  ,count(*) as exams_passed
from
   Students s
  ,Student_Exam se
  ,Exam e
where
   s.s_id = se.s_id
   and se.exam_id = e.exam_id
   and e.date_taken between #1/1/2010# and #12/31/2010#
   and se.pass = 'Yes'
group by
   s.s_id

Upvotes: 0

Hogan
Hogan

Reputation: 70523

select s.s_id as student_id, count(*)
FROM students s
left join student_exam se on s.s_id = se.s_id and se.pass='yes'
left join exam e on se.exam_id = e.exam_id
group by s.s_id

I would suggest you change pass to a non text field. If it is just going to have yes and no then it should be boolean.

Upvotes: 1

Related Questions