Member Avatar for Member #1127894

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;

  • 3 Contributors
  • 2 Replies
  • 232 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by Amr.Mohammad87

Dani AI

Generated

Two issues explain the error and the unexpected results: a stray or incomplete qualification (the trailing dot) and mixing comma-style joins with an explicit LEFT JOIN. correctly flagged the stray dot; is also right that every non-aggregated column in the SELECT must appear in the GROUP BY (unless wrapped in an aggregate).

Use consistent, explicit JOINs and table aliases so column qualification is clear, and put the ON clause immediately after each JOIN. Decide whether students with zero enrollments should appear — use LEFT JOIN to include them, INNER JOIN to exclude them. Use COUNT(column) to count non-null joined rows, or COUNT(DISTINCT ...) if the enrollment table can contain duplicate rows per course.

A compact, corrected pattern (adjust names to match the actual schema) looks like this:

SELECT s.s_id     AS StudentID,
       s.s_last   AS LastName,
       COUNT(e.uc_course_section) AS CourseCount
FROM uc_student AS s
LEFT JOIN uc_enrollment AS e
  ON e.uc_student = s.s_id
GROUP BY s.s_id, s.s_last;

Troubleshooting checklist: remove any stray punctuation (trailing dots), avoid mixing comma joins with explicit JOIN syntax, alias every table to disambiguate same-named columns, and verify the ON expression references the correct columns (foreign key = primary key). If only unique courses per student are wanted, change the COUNT to COUNT(DISTINCT e.uc_course_section). To see the busiest students first, add ORDER BY CourseCount DESC.

Recommended Answers

All 2 Replies

Member Avatar for 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)

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.