CODEWITHSUNDEEP
mysqlsqlgroup-by
shasi kanth
shasi kanth

Reputation: 7094

mysql group by on sum column

I am having a scenario where students of a college do a research on a subject. Each subject has some value (marks) for it. A student can do research on multiple subjects.

I have the following table hierarchy:

student (s)
---------------
student_id  subject_id

1           2
2           1
2           3
3           1
3           3
4           2
4           3
.....


research_subjects (r)
-----------------------------
id      value

1       5
2       10
3       20
4       40
....

Now i am fetching the student records along with their total research value with this query:

select student_id, sum(r.value) as total from student s inner join research_subjects r on s.subject_id=r.id group by student_id

This gives results like the following:

student_id  total

1           10
2           25
3           25
4           30

As you see, the results are grouped by student_id. But what i want is to group the results by total value. So i want to get rid of duplicate rows for the total, in the output. (i.e., have only 1 record with the total=25).

I tried using: group by total in the query (instead of group by student_id), but it gives an error. Is there any other way of grouping the results by the column that contains 'sum' value?

Upvotes: 0

Views: 351

Answers (2)

Milen
Milen

Reputation: 8867

Try this:

select Count(student_id) as students, total
from
(
    select student_id, sum(r.value) as total 
    from student s inner join research r on s.subject_id=r.id 
    group by student_id
) s
group by total

Or this:

 select Min(student_id) as student_id, total
    from
    (
        select student_id, sum(r.value) as total 
        from student s inner join research r on s.subject_id=r.id 
        group by student_id
    ) s
    group by total

Upvotes: 1

DirkNM
DirkNM

Reputation: 2664

Try this:

select count(student_id), total
  from ( 
    select student_id, sum(r.value) as total 
     from student s 
     inner 
     join research r on s.subject_id=r.id group by student_id
     ) s2
 group by total

Upvotes: 2

Related Questions