Question 20284 in Mysql Question posted in
The official MySQL documentation can be found here.

How to use aggregate function with a case statement and a where condition – Mysql

I am trying to get max(level) and sum(hours) until someone reaches level 5 for the first time.
This is my input

date level hours
1/1/2021 1 1
1/2/2021 2 1
1/3/2021 2 2
1/4/2021 3 1
1/5/2021 4 1
1/6/2021 5 3
1/7/2021 5 1
1/8/2021 5 1

Output before the aggregate

date level hours
1/1/2021 1 1
1/2/2021 2 1
1/3/2021 2 2
1/4/2021 3 1
1/5/2021 4 1
1/6/2021 5 3

Final output

select max(level) as level, sum(hours) as total_hours
from final

level hours
5 9

I am not sure how to use case statement with sum where I wanted to limit my data when it reached level 5 for the first time

This should calculate sum of hours if level is less than 5 as well

Tags:

4

Answers


  1. 0 
    SELECT MAX(level) AS level,
       SUM(CASE WHEN date >= (SELECT MIN(date) FROM input WHERE level = 5)
                THEN 0 ELSE hours END) AS total_hours
FROM input;
    • finds the earliest date when level is equal to 5.
    • CASE limits SUM of hours to 0 for all rows with a date greater than or equal to this earliest date, stopping the sum when level 5 is reached for the first time
    • MAX function finds the maximum level value in the table
    Login or Signup to reply.
  2. 0

    Assuming that your table has id column, here is the query:

    SELECT MAX(level), SUM(hours) FROM table_name
WHERE id <= (SELECT MIN(id) FROM table_name WHERE level = 5);

    Condition id <= (SELECT MIN(id) FROM table_name WHERE level = 5) will restrict your data to a record where it reached level 5 for the first time

    Login or Signup to reply.
  3. 0

    You should try this

    SELECT MAX(level), SUM(hours) FROM Table1 WHERE date <= (SELECT MIN(date) FROM Table1 WHERE level = 5 limit 1);

    • Minimum DATE required with Minumum Level = 5

    SELECT MAX(level), SUM(hours) FROM Table1 WHERE date <= (SELECT MIN(date) FROM Table1 WHERE level = 5 limit 1) or date <= (SELECT MAX(date) FROM Table1 WHERE level < 5)

    And – Maximum DATE required with Level < 5

    DEMO

    Login or Signup to reply.
  4. 0

    You can try this query this will works..

    select max(level) Level,sum(hours) Hours from Table1 where date <= 
(select max(date) from Table1 where hours = (select Max(hours) from Table1))

    Result

    Edited: If if the same hours occurred, then it will take the exact sum.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search