Writing a statement using join, count, and group by

[calypso11]

Write a select statement that will display student id, student last name and the number of courses a student has been enrolled. You will need to join two table, use a COUNT aggregate and a GROUP BY clause in your SELECT statement.

That is the question. I've written the following query but I keep getting incorrect syntax near the keyword 'Group'. I don't know how to fix it or really what the error is. I'm not looking for anyone to solve this for me, just a good push in the right direction. I'm not even sure this statement will display correctly.

SELECT s_id.uc_student,COUNT(max_enrl.uc_course_section) AS NumberOfOrders FROM uc_course_section,uc_student,uc_enrollment LEFT JOIN uc_student ON uc_enrollment.uc_student=uc_enrollment. GROUP BY s_id,s_last;

[JOSheaIV]

When you do a GROUP BY, it has to be by the columns selected (with the exception of those that are like Count)

So you would need something like

SELECT s_id, s_last, MAX(s_id.uc_student) AS s_id.uc_student, COUNT(max_enrl.uc_course_section) AS NumberOfOrders

As your select statement. This is just an example of couse (is s_id a column or a table? if it's a table then I used it wrong). Also notice how I did the "MAX". You can do that or a "MIN" to get around using a column in the GROUP BY, kind of like how I said Counts are excluded.

But can you please verify for me what s_id.uc_student is and what s_id is? Maybe I am totally overlooking something here ... wait are these meant to be Table Alias? If so then you need to specify that as well (and once again, if that's the case, does that mean you are trying to group by a table? I don't think that's possible)

[Amr.Mohammad87]

SELECT s_id.uc_student,COUNT(max_enrl.uc_course_section) AS NumberOfOrders FROM uc_course_section,uc_student,uc_enrollment LEFT JOIN uc_student ON uc_enrollment.uc_student=uc_enrollment.**NOTICE** GROUP BY s_id,s_last;

Can you notice the word NOTICE I wrote within the code. You put the "." operator and you do not put the column name after that is why you are getting incorrect sytanx error. I hope that help.