CODEWITHSUNDEEP
mysqlsum
bernard
bernard

Reputation: 21

how to return last row in specific sum?

imagine we have 1 row which is students that contain, id, name, marks and rank. write query that return the last name of student where marks is equal to 100 ordered by grade. example

 - id | name | marks | grade |
 - 01 | Jeff | 40    | 1 |
 - 02 | Annie| 40    | 3 |
 - 03 | Ramy | 20    | 5 |
 - 04 | Jenny| 20    | 2 |

so the result should return

Annie

because Annie is the last row of the sum of marks where marks is equal to 100. Jeff is the first cause based on grade he's equal to 1 so he should be entered first, second is Jenny and third is Annie. Jeff(40)+Jenny(20)+Annie(40) = 100

Upvotes: 0

Views: 78

Answers (3)

Vishal
Vishal

Reputation: 26

As mentioned the database structure above, Below is one of the way to get the output

select name from (select * from (SELECT id,name,grade,marks, @total := @total + marks AS total FROM (stud, (select @total := 0) t) order by grade ) t WHERE total <=100 ) final_view order by grade desc limit 1

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Keep the cumulative sum of marks to a variable. And use this as a sub-query and select the row having the total is 100. But if no row having the cumulative total as 100, then wont't get any result.

Query

set @total := 0;
select `id`, `name`, `marks`, `grade` from(
    select `id`, `name`, `marks`, `grade`, (@total := @total + `marks`) as `total`
    from `your_table_name`
    order by `grade`
) as `t`
where `t`.`total` = 100;

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

You can make a running sum MySQL's user variable.
This query should work from MySQL 5.1 and up.

Query

SELECT 
 Table1_alias.name
FROM (

  SELECT 
      Table1.name
   , (@running_marks_sum := @running_marks_sum + Table1.marks) AS running_marks_sum
  FROM 
   Table1
  CROSS JOIN (SELECT @running_marks_sum := 0) AS init_user_param
  ORDER BY 
    Table1.grade ASC  
) AS Table1_alias
WHERE 
 Table1_alias.running_marks_sum = 100

Result

| name  |
| ----- |
| Annie |

View on DB Fiddle

MySQL 8.0+ only

Query

SELECT 
 Table1_alias.name
FROM (

  SELECT 
     Table1.name
   , SUM(Table1.marks) OVER(ORDER BY Table1.grade) AS running_marks_sum
  FROM 
    Table1

) AS Table1_alias 
WHERE 
  Table1_alias.running_marks_sum = 100;

Result

| name  |
| ----- |
| Annie |

View on DB Fiddle

Upvotes: 1

Related Questions