SQL View only returns one row

Question

I'm trying to create a view that returns the amount of courses each Californian student is enrolled in. There are 4 CA students listed in my 'Students' table, so it should return that many rows.

create or replace view s2018_courses as 
select Students.*, COUNT(Current_Schedule.ID) EnrolledCourses
from Students, Current_Schedule
where Students.ID = Current_Schedule.ID AND state='CA';

This query, however, only returns a single row with one student's information, and the total number of courses all CA students are in (in this case 14, since each student is enrolled in 3-5 classes).

I created a view similar to this recently (in a different DB) and it worked well and executed multiple rows, so I'm not sure what is going wrong? Sorry if this is a confusing question, I'm new to SQL and StackOverflow!! Thank you in advance for any advice!

score 1 · Accepted Answer · answered Nov 21 '17 at 01:16

You are missing an aggregation step in your query/view:

CREATE OR REPLACE VIEW s2018_courses AS 
SELECT
    s.ID, COUNT(cs.ID) EnrolledCourses
FROM Students s
INNER JOIN Current_Schedule cs
    ON s.ID = cs.ID
WHERE
    state = 'CA'
GROUP BY
    s.ID;

The logical problem with your current query is that you are using COUNT(*) without GROUP BY, and MySQL is interpreting this to mean that you want to take the count of the entire table. Note also that I only select ID in my query above, because this is what is being used to aggregate.

SQL View only returns one row

1 Answers1