COUNT Query Help

[db1993]

Hello, I am having difficulties with this class work question

 

List the various courseTitles, and for each course with the number of 

female students registered. Order the list by descending counts.

 

 

The query I have used so far:

 

SELECT course.courseTitle, COUNT(DISTINCT student.sex)

FROM course, student

WHERE student.sex = 'f'

 

 

There are 2 Course Titles but the output of the above query only displays the first Course Title instead of both.

Also, instead of displaying the count of 'F' (females) as 6 it displays it as 1

[PravinS]

try using GROUP BY clause  like this

 

SELECT course.courseTitle, COUNT(DISTINCT student.sex)
FROM course, student
WHERE student.sex = 'f' GROUP BY course.courseTitle

Edited October 31, 2013 by PravinS

[Barand]

COUNT(DISTINCT sex)  counts how many different sexes there are where the sex = F ie 1.

 

You join "courses, students" is wrong. This will give a cartesian join, joining every course record with every student record. If you have 10 courses and 100 students you will get 1000 rows returned.

 

You must specify how the tables are related eg

FROM course JOIN student ON course.somefield = student.otherfield