SQL using both select and count

Question

I am trying to answer this question for my class (https://i.stack.imgur.com/piNMf.png) given this database https://i.stack.imgur.com/nrP0a.png) my database schema is:

CREATE TABLE COURSE (
  Course_name varchar(50),
  Course_number varchar(50), 
  Credit_hours varchar(10), 
  Department varchar(50)
);
CREATE TABLE SECTION (
  Course_number varchar(50),
  Section_identifier int(50), 
  Semester varchar(10), 
  Year varchar(50),
  Instructor varchar(50)
);
CREATE TABLE GRADE_REPORT (
  Student_number varchar(50),
  Section_identifier varchar(50), 
  Grade varchar(10));
 
INSERT INTO COURSE (Course_name, Course_number, Credit_hours, Department)
VALUES ('Intro to Computer Science', 'CS1310', '4', 'CS'); 
INSERT INTO COURSE (Course_name, Course_number, Credit_hours, Department)
VALUES ('Data Structures', 'CS3320', '4', 'CS'); 
INSERT INTO COURSE (Course_name, Course_number, Credit_hours, Department)
VALUES ('Discrete Mathematics', 'MATH2410', '3', 'MATH'); 
INSERT INTO COURSE (Course_name, Course_number, Credit_hours, Department)
VALUES ('Database', 'CS3380', '3', 'CS'); 

INSERT INTO SECTION (Section_identifier, Course_number, Semester, Year, Instructor)
VALUES('85', 'MATH2410', 'Fall', '07', 'King');
INSERT INTO SECTION (Section_identifier, Course_number, Semester, Year, Instructor)
VALUES('92', 'CS1310', 'Fall', '07', 'Anderson');
INSERT INTO SECTION (Section_identifier, Course_number, Semester, Year, Instructor)
VALUES('102', 'CS3320', 'Spring', '08', 'Knuth');
INSERT INTO SECTION (Section_identifier, Course_number, Semester, Year, Instructor)
VALUES('112', 'MATH2410', 'Fall', '08', 'Chang');
INSERT INTO SECTION (Section_identifier, Course_number, Semester, Year, Instructor)
VALUES('119', 'CS1310', 'Fall', '08', 'Anderson');
INSERT INTO SECTION (Section_identifier, Course_number, Semester, Year, Instructor)
VALUES('135', 'CS3380', 'Fall', '08', 'Stone');

INSERT INTO GRADE_REPORT (Student_number, Section_identifier, Grade)
VALUES ('17', '112', 'B'), ('17', '119', 'C'), ('8', '85', 'A'),
('8', '92', 'A'), ('8', '102', 'B'), ('8', '135', 'A');

however to get the course number, semester, and year, AND amount of students it wont let me combine the select and count function

I tried to do it like this:

SELECT SECTION.Course_number, Semester, Year, COUNT(Student_number)
FROM COURSE
JOIN SECTION 
ON COURSE.Course_number= SECTION.Course_number
JOIN GRADE_REPORT
ON GRADE_REPORT.Section_identifier=SECTION.Section_identifier
WHERE Department='CS';

which gave me the error: Query Error: Error:

ER_MIX_OF_GROUP_FUNC_AND_FIELDS: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.SECTION.Course_number'; this is incompatible with sql_mode=only_full_group_by

and i tried to enclose it like this:

SELECT SECTION.Course_number, Semester, Year, (COUNT(Student_number))
FROM COURSE
JOIN SECTION 
ON COURSE.Course_number= SECTION.Course_number
JOIN GRADE_REPORT
ON GRADE_REPORT.Section_identifier=SECTION.Section_identifier
WHERE Department='CS';

which gave the same error, not sure what i should do here

Does this answer your question? [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) — Ron, Apr 01 '23 at 09:10

score 0 · Answer 1 · answered Apr 01 '23 at 09:44

You will not need to group your data by section, since every section has only one entry in GRADE_REPORT table :

SELECT SECTION.Course_number, Semester, Year, Student_number
FROM COURSE
JOIN SECTION 
ON COURSE.Course_number= SECTION.Course_number
JOIN GRADE_REPORT
ON GRADE_REPORT.Section_identifier=SECTION.Section_identifier
WHERE Department='CS';

If there are multi records per section then you will need to use group by to groupe your data, then SUM() instead of COUNT() to get your desired output :

SELECT SECTION.Course_number, Semester, Year, SUM(Student_number)
FROM COURSE
JOIN SECTION 
ON COURSE.Course_number= SECTION.Course_number
JOIN GRADE_REPORT
ON GRADE_REPORT.Section_identifier=SECTION.Section_identifier
WHERE Department='CS'
group by SECTION.Section_identifier, SECTION.Course_number, Semester, Year

SQL using both select and count

1 Answers1