So here is the table:
Name Indicator Amount
Anson 1 3.5
Anson 2 2.6
Anson 3 8.4
Anson 4 3.2
Anson 6 3.7
Ben 1 7
Ben 3 4.7
Ben 4 3.6
How can I get the sum amount when the indicator is consecutive? This is the result I want:
Name Indicator Amount
Anson 1,2,3,4 17.7
Anson 6 3.7
Ben 1 7
Ben 3,4 8.3
i tried this but it could only combine two consecutive indicator.
select name_, indicator, amount, sum_amount,
(CASE WHEN diff = 1 THEN sum_amount ELSE amount END) as final_amount,
(CASE WHEN diff = 1 THEN convert(varchar(10),prev_ind)+',' +convert(varchar(10),indicator) ELSE convert(varchar(10),indicator) END) as indicator
FROM (
select name_, indicator, prev_ind, sum(indicator-prev_ind) as diff, amount,
sum(amount + prev_amount) sum_amount
from (
select name_, indicator, lag(indicator,1,0) over (order by name_, indicator) prev_ind, amount,
lag(amount,1,0) over (order by name_) prev_amount
from tabb
)g
where indicator <> 1
group by name_, indicator, prev_ind, amount
)u
Thanks!
Sybase supports the list function. I think the easiest way to get consecutive values is to subtract row_number() from indicator. The difference is constant for consecutive items:
select name, list(indicator), sum(amount)
from (select t.*,
(row_number() over (partition by name order by indicator) - indicator
) grp
from tabb t
) t
group by name, grp;
To be honest, I don't see that using list() really adds to the query. You can also just put in the minimum and maximum values:
select name,
cast(min(indicator) as varchar(255)) + '-' + cast(max(indicator) as varchar(255)) as range,
sum(amount)
from (select t.*, cast(indicator as varchar(255)) as inds
(row_number() over (partition by name order by indicator) - indicator
) grp
from tabb t
) t
group by name, grp;