CODEWITHSUNDEEP
mysqlsqldatabase
DevStacker
DevStacker

Reputation: 735

SQL Not showing all rows

I am writing mysql code and cannot find the solution.

the question is "List the various courseTitles, and for each course with the number of female students registered. Order the list by descending counts."

There are 2 courses, one being Software Engineering and the other being Information Systems.

There are 6 female students in the database and all 6 do both IT and English (showing 12 results all together)

When I do the following sql statement,

SELECT courseTitle, sex
FROM Course, Student
WHERE sex = 'f';

it displays all the Females which partake in IT and English (which comes up with 12 instances).

However, when I use count to show how many females do SE and how many do IS, 

SELECT courseTitle, COUNT(sex) as sex
FROM Course, Student
WHERE sex = 'f';

It only displays one course title (SE) and displays the number 12 (so 12 females), but it does not display the other course title (IS).

The answer should display 6 for SE and 6 for IS and I am at a dead end as to why this is not working.

Thank You

Upvotes: 0

Views: 2589

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

You need to add a group by clause

SELECT courseTitle, 
       COUNT(sex) as sex 
FROM Course, Student WHERE sex = 'f'
group by courseTitle;

This will display a row for each courseTitle, instead of counting over the entire found data set.

Upvotes: 4

Related Questions