How can i get the total amount with consecutive indicator?

Question

So here is the table:

Name    Indicator   Amount
Anson       1         3.5
Anson       2         2.6
Anson       4         8.4
Anson       5         3.2
Ben         1         7
Ben         3         4.7
Ben         4         3.6

How can I get the sum amount if the indicator is consecutive? This is the result I want:

Name    Indicator   Amount
Anson       1,2       6.1
Anson       4,5       11.6
Ben         1         7
Ben         3,4       8.3

Thanks!

Answer 1

Try this, it works in Oracle, so I assume it should in mysql. It's not pretty... Use Lag function to bring the previous name's indicator and amount. Then find the difference between the indicators and sum the amounts. Finally use a CASE statement to format the returned results according.

select name, 
CASE diff WHEN 1 THEN to_char(prev_ind||','||indicator)
                                 ELSE to_char(indicator) END as Indicator,
CASE diff WHEN 1 then to_char(sum_amount) 
             else to_char(amounnt)   END as Amount
FROM (     
select name, indicator, prev_ind, sum(indicator-prev_ind) as diff, amounnt,
sum(amounnt + prev_amount) sum_amount
from (

select name, indicator, lag(indicator,1,0) over (order by name) prev_ind, amounnt,
lag(amounnt,1,0) over (order by name) prev_amount
from tmp1
order by name, indicator

)
group by  name, indicator, prev_ind, amounnt
order by name, indicator)
where diff != 2
order by name;