CODEWITHSUNDEEP
mysqlsqldatabase
51k
51k

Reputation: 1443

Class wise, student name who has max aggregate of marks

Suppose i have a table as follows: 

Class   |   Subject | Student   | Marks
----------------------------------------
1       |   Maths   |   A       |   70   
1       |   Eng     |   B       |   80
1       |   IT      |   A       |   90 
1       |   IT      |   C       |   80 
2       |   Maths   |   D       |   60   
2       |   Eng     |   E       |   75
2       |   Maths   |   E       |   90 
2       |   IT      |   F       |   80 
3       |   Maths   |   A       |   160   
3       |   Eng     |   B       |   165
3       |   IT      |   G       |   90

I want the output as 

Class   |   Student     | Marks
----------------------------------------
1       |       A       |   160   
2       |       E       |   165
3       |       B       |   165

i.e. Result contains class wise, student name who has max aggregate of marks. How to write a SQL query for this? e.g. for class 1, student A has 70+90 = 160, which becomes maximum over B and C both with 80.

Upvotes: 2

Views: 11732

Answers (7)

Puneet Agrawal
Puneet Agrawal

Reputation: 31

    SQL> with cte as
      2  (select class, student, sum(marks) marks
      3  from engineer
      4  group by class, student
      5  order by class)
      6  select class, student, marks
      7  from (select class, student, marks, dense_rank() over(partition by class order by marks desc) rank
      8  from cte)
      9  where rank=1;

         CLASS S      MARKS
    ---------- - ----------
             1 A        160
             2 E        165
             3 B        165

    SQL>

Here the most important inner query is for cte table. Resulr set created for this one will be as below.

SQL> select class, student, sum(marks)
  2  from engineer
  3  group by class, student
  4  order by class;

     CLASS S SUM(MARKS)
---------- - ----------
         1 A        160
         1 B         80
         1 C         80
         2 D         60
         2 E        165
         2 F         80
         3 A        160
         3 B        165
         3 G         90

9 rows selected.

Upvotes: -1

Andomar
Andomar

Reputation: 238076

One solution is to calculate the maximum points a student has per class, and use that as a filtering join:

select  ClassStudentSum.*
from    (
        select  class
        ,       student
        ,       sum(Marks) as SumMarks
        from    YourTable
        group by
                class
        ,       student
        ) as ClassStudentSum
join    (
        select  class
        ,       max(SumMarks) as MaxSumMarks
        from    (
                select  class
                ,       student
                ,       sum(Marks) as SumMarks
                from    YourTable
                group by
                        class
                ,       student
                ) ClassStudentSum2
        group by
                class
        ) MaxPerClass
on      MaxPerClass.class = ClassStudentSum.class
        and MaxPerClass.MaxSumMarks = ClassStudentSum.SumMarks

Live example at SQL Fiddle.

Upvotes: 5

abhijitsinghh
abhijitsinghh

Reputation: 41

Here is a simpler query, I guess. It works fine. No need of joins.

SELECT class,
(
SELECT student FROM yourtable
WHERE class = YT.class GROUP BY student
ORDER BY SUM(marks) DESC
LIMIT 1
) AS student,
(
SELECT SUM(marks) FROM yourtable
WHERE class = YT.class GROUP BY student
ORDER BY SUM(marks) DESC
LIMIT 1
) AS marks
FROM yourtable AS YT
GROUP BY class

Upvotes: 0

Soham Chakraborty
Soham Chakraborty

Reputation: 11

The correct query is:

select class, student, sums.mark
from (select class, student, sum(marks) as mark
      from student
      group by class, student
      order by mark desc) sums
group by class

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

A more traditional (and so slower) approach...

SELECT x.*
  FROM
     ( SELECT class
            , student
            , SUM(marks) ttl_marks
         FROM yourtable 
        GROUP
           BY class
            , student
     ) x
  LEFT 
  JOIN
     ( SELECT class
            , student
            , SUM(marks) ttl_marks
         FROM yourtable 
        GROUP
           BY class
            , student
     ) y
    ON y.class = x.class
   AND y.ttl_marks > x.ttl_marks
 WHERE y.class IS NULL;

Upvotes: 2

Shailesh
Shailesh

Reputation: 990

Use this Statement.it is works

   select Class,Student,Marks 
from(
    select Class,Student,Marks,Dense_rank() over( partition by class order by marks desc) Rank
      from(Select Class, Student,Max(Marks) Marks 
        from(select Class, Student,Sum(Marks) Marks from Temp14
          group by Class,Student
          order by 1)
        group by Class, Student
        order by 1)
    )
where Rank=1

Try this one.

Upvotes: 0

Meherzad
Meherzad

Reputation: 8553

Try this query

Query 1:

select a.*, if(@prv=class, 1, 0) as flag, @prv:=class from 
(select class,student, sum(marks) as total from table1 
group by class, student
order by class, total desc)a join (select @prv:=0)tmp
where if(@prv=class, 1, 0) = 0

SQL FIDDLE:

| CLASS | STUDENT | TOTAL | FLAG | @PRV:=CLASS |
------------------------------------------------
|     1 |       A |   160 |    0 |           1 |
|     2 |       E |   165 |    0 |           2 |
|     3 |       B |   165 |    0 |           3 |

Hope this helps

Upvotes: 1

Related Questions