0

I am trying to achieve the below but was not able to so far, any help would be greatly appreciated. I have this data (sorted from a query by id, anchor, date, and time) that I wish to group by common anchor :

id anchor date        time     'group' (the value to get)
3  2      2019-01-01  07:00     1
4  2      2019-01-01  08:00     1
5  3      2019-01-01  15:00     2
7  3      2019-01-01  16:00     2
10 3      2019-01-01  17:00     2

I'm looking to do a query in postgres where I can select this data and foreach set of common anchors, have a 'group number' I then need a query to sum the anchor of points of same group, example above would become:

anchor sum group
2      4   1
3      9   2

thanks!

EDIT: McNets solution works perfect, I have another case, with below data. The anchor repeats but after a change of anchor: they're sorted by time, first it was anchor 2, then anchor 3, then again anchor 2. I need to group after the change (ids 11 & 12) to have a new group number in this case

id anchor date        time     'group' (the value to get)
3  2      2019-01-01  07:00     1
4  2      2019-01-01  08:00     1
5  3      2019-01-01  15:00     2
7  3      2019-01-01  16:00     2
10 3      2019-01-01  17:00     2
11 2      2019-01-01  18:00     3
12 2      2019-01-01  19:00     3
1

Use DENSE_RANK function:

select
    anchor, sum(anchor) as "sum", grp  as "group"
from
    (select 
         anchor, dense_rank() over (order by anchor) as grp
     from
         t) t1
group by
    anchor, grp
order by
    grp;
anchor | sum | group
-----: | --: | ----:
     2 |   4 |     1
     3 |   9 |     2

db<>fiddle here

More examples about DENSE_RANK here.

Even though it is preferable to add a new question when there is a significant change, this is the answer at your edited question.

select anchor,
       sum(anchor) as "sum",
       grp as "group"
from (
      select anchor, dt, tm,
             sum(rst) over (order by dt, tm) as grp
      from (
            select anchor, dt, tm,
                   case when coalesce(lag(anchor) 
                                      over (order by dt, tm), 0) <> anchor 
                        then 1 end as rst
            from   t
           ) t1
     ) t2
group by anchor, grp
order by grp;
anchor | sum | group
-----: | --: | ----:
     2 |   4 |     1
     3 |   9 |     2
     2 |   4 |     3

db<>fiddle here

  • ah perfect! thanks...another tricky matter came up, i put it as an edit, would love to hear your thoughts on that...boggled my mind for a while – roy naufal Aug 30 at 13:06
  • thanks a lot, did the job perfectly..did some modifications, but was able to undertand the logic...some new stuff to me here xD – roy naufal Aug 30 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.