Count single record if duplicate exists (1 Viewer)

I want to do a count of total courses a student has passed, not including a duplicated course.

Students are required to pass 5 courses. But may have taken a course more than once to earn a higher grade even if already passed. Is there any "Count" expression or another expression to count total passed course, but not include the duplicate, for each student. Preferably keep the lower grade, for GPA calculation.

Example data below:
(if I do plain >=5 count for the below data, it counts both duplicates, which I don't want)

Course1-A
Course2-C (duplicate course, although different grade)
Course2-D (duplicate course, although different grade)
Course3-B
Course4-A
Course5-C

I need it to count only the following:
Course1-A
Course2-D (duplicate course, although different grade)
Course3-B
Course4-A
Course5-C

Thank you all in advance for any assistance.

Select Count(*) From (Select Course From table1 Group By Course);

Or

Select Count(*) From (Select Distinct Course From table1);

You are asking for 2 different things--a count of unique courses passed and the lowest grade of each course passed. The example you provided showed how you wanted the lowest grade for each course passed, below is the SQL for that:


arnelgp gave you the other.

arnelgp said:

Select Count(*) From (Select Course From table1 Group By Course);

Or

Select Count(*) From (Select Distinct Course From table1);

Click to expand...

Where in my SQL should I add your coding?

Arnel, Plog

I want to do a count of total courses a student has passed

Click to expand...

You'll need to include Students in your query grouping