Calculate with consecutive indicator

Question

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!

What would you gain by just not processing the results returned from a sort on "Name" and "Indicator"? This seems more about processing sorted results rather than constructing some SQL to do that in a server operation. — Blakes Seven, Jul 03 '15 at 09:59
i tried this query but it could only combine two consecutive indicator @RaduGheorghiu — gsql, Jul 03 '15 at 10:05

T.Y. Kucuk · Answer 1 · 2015-07-03T11:25:14.467

0

You can solve this problem using lag() / lead() functions by grouping IsConsecutive criteria and name column with following approach;

  SELECT P.NAME,
     LISTAGG(P.INDICATOR,',') WITHIN GROUP(ORDER BY P.INDICATOR) INDICATORS,
     SUM (P.AMOUNT) TOTAL
FROM (SELECT Q.AMOUNT,
             Q.NAME,
             Q.INDICATOR,
             CASE WHEN Q.INDICATOR +1 = Q.AFTER OR Q.INDICATOR-1 = Q.BEFORE THEN 'IsConsecutive' ELSE TO_CHAR(Q.INDICATOR) END GRP
        FROM (SELECT T.AMOUNT,
                     T.NAME,
                     T.INDICATOR,
                     LAG (T.INDICATOR) OVER (PARTITION BY T.NAME ORDER BY T.INDICATOR) BEFORE,
                     LEAD (T.INDICATOR)  OVER (PARTITION BY T.NAME ORDER BY T.INDICATOR) AFTER
                FROM YOUR_TABLE T
             )Q
             )P
GROUP BY P.GRP, P.NAME
ORDER BY P.NAME

I wrote this SQL statement for Oracle Database. But you can easily convert it if you want. It gives the desired output:

Name     Indicators  Total
Anson    1,2,3,4     17.7
Anson    6           3.7
Ben      1           7
Ben      3,4         8.3

edited Jul 03 '15 at 11:25

answered Jul 03 '15 at 11:07

T.Y. Kucuk

447
8
24

This only works if a name never has the same indicator twice. If you put in eg. ('Anson', 2, 3.5) at the end of the table you would not get expected result. – Jon Tofte-Hansen Jul 03 '15 at 12:34
What should be supposed to be when the name has the same indicator twice or more? – T.Y. Kucuk Jul 03 '15 at 14:28
Try it out and you will see what I mean. Your code is fine. I just point out, that the `name, indicator` set has to be consecutive – Jon Tofte-Hansen Jul 03 '15 at 14:37
If one wish to incorporate additional indicator into the comma separated list, he should add `OR Q.INDICATOR = Q.AFTER` as a last condition inside the case. – T.Y. Kucuk Jul 03 '15 at 15:05

score 0 · Accepted Answer · answered Jul 03 '15 at 12:24

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;

Calculate with consecutive indicator

2 Answers2