Select columns other than the one specified in GROUP BY clause

Question

Is there a way to select columns other the one specified in the group by clause? Let's say I have the following schema:

Student(id, name, age), Course(id, name, credit), Enrollment(student_id, course_id, grade)

I want to query for each course the following: course's name, student_count.

I came up with workaround, but I was wondering if there's a cleaner way to do this:

SELECT MAX(c.name), COUNT(distinct e.student_id)
FROM Enrollment e
INNER JOIN Course c ON c.id = e.course_id
GROUP BY e.course_id;

You should post some sample data for each table and the desired result. — Taryn, Apr 11 '13 at 19:47

score 3 · Answer 1 · answered Apr 11 '13 at 19:53

You might want to copy this DDL, adjust it to match your schema, and paste it into your question.

create table Student(
  student_id integer primary key, 
  student_name varchar(35) not null, 
  age int not null default 20
);

create table Course(
  course_id integer primary key, 
  course_name varchar(35) not null, 
  credit integer not null default 3
);

create table Enrollment(
  student_id integer not null references Student (student_id), 
  course_id integer not null references Course (course_id), 
  primary key (student_id, course_id),
  grade char(1) not null
);

insert into student values 
(1, 'a', 20),
(2, 'b', 20),
(3, 'c', 20);

insert into course values
(1, 'course 1', 3),
(2, 'course 2', 3),
(3, 'course 3', 3);

insert into enrollment values
(1, 1, 'b'),
(2, 1, 'b'),
(3, 1, 'b'),
(1, 2, 'b'),
(2, 2, 'b'),
(3, 3, 'b');

Now, you can get the number of students enrolled in each course by querying only the "enrollment" table.

select course_id, count(student_id) num_students
from enrollment
group by course_id
order by course_id;

course_id  num_students
--
1          3
2          2
3          1

All that remains is to get the corresponding course name. To do that, you just join the table "Course" with the query we just wrote.

select course.course_name, course_enrollment.num_students
from course
inner join (select course_id, count(student_id) num_students
            from enrollment
            group by course_id) course_enrollment
        on course.course_id = course_enrollment.course_id;

course_name  num_students
--
course 1     3
course 3     1
course 2     2

score 2 · Accepted Answer · answered Apr 11 '13 at 19:36

2

No, you can't. But you can extend GROUP BY with c.name:

SELECT MAX(c.name), COUNT(distinct e.student_id)
FROM Enrollment e
INNER JOIN Course c ON c.id = e.course_id
GROUP BY e.course_id, c.name

Because e.course_id is unique, it won't change results.

answered Apr 11 '13 at 19:36

MarcinJuraszek

124,003
15
196
263

Right! I didn't think of that! But what if the schema is more complicated. Let's say I have Employee (id, name, salary), Department(id, name, manager_id), and WorksIn(employee_id, department_id) and I want to do similar query. That is for each department, find the emp count, dept name, manager name, manager salary. (where manager_id references Employee) I was wondering if I do `GROUP BY d.did, m.name, m.salary`, I would get the correct result? – 0x56794E Apr 11 '13 at 19:46
So since d.did is unique, what comes afterwards, in this case, doesn't really matter? – 0x56794E Apr 11 '13 at 19:50
No, you can't say it doesn't really matter in general. But in that particular case it doesn't. – MarcinJuraszek Apr 11 '13 at 19:51

Select columns other than the one specified in GROUP BY clause

2 Answers2