2

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!

gsql
  • 45
  • 4

2 Answers2

1

Unfortunately, MySQL does not have window functions. But, if you had a sequence of values that incremented by 1, then the difference between that sequence and Indicator would be constant for each group. You can create such a sequence using variables.

The rest is just arithmetic and aggregation:

select name, group_concat(indicator), sum(amount)
from (select t.*, (@rn := @rn + 1) as rn
      from table t cross join
           (select @rn := 0) params
      order by name, indicator
     ) t
group by name, (rn - indicator)
order by name, min(indicator);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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;
LagartijaNick
  • 18
  • 3