'Error when trying to use count and group by

Here is my query:

SELECT DISTINCT takes.semester, takes.sec_id, takes.course_id, instructor.name, COUNT(takes.ID) 
FROM takes 
INNER JOIN teaches 
ON takes.course_id = teaches.course_id AND takes.semester = teaches.semester AND takes.sec_id = teaches.sec_id AND takes.year = teaches.year 
INNER JOIN instructor 
ON instructor.ID = teaches.ID 
WHERE takes.year = 2019
GROUP BY takes.course_id

This gave me an error:

#1064 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated columnExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ...

How do I fix this? I'm using group by so to count the number of IDs, but this error popped up. I don't know how to fix this. Please help. Thanks!

mysqlsql


Solution 1:[1]

You need to move the GROUP BY to the end of the query as well as include the non-aggregate columns in your GROUP BY.

SELECT takes.semester, 
                takes.sec_id, 
                takes.course_id, 
                instructor.name, 
                COUNT(takes.ID) 
FROM takes 
INNER JOIN teaches 
  ON takes.course_id = teaches.course_id 
    AND takes.semester = teaches.semester 
    AND takes.sec_id = teaches.sec_id AND takes.year = teaches.year 
INNER JOIN instructor ON instructor.ID = teaches.ID 
WHERE takes.year = 2019
GROUP BY takes.semester, takes.sec_id, takes.course_id, instructor.name

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions