using group by with range restriction

[kyle]

Hi,
I have a question regarding aggregation using group by. Assuming I have a below table

Code: Select all

start | end | value
1 | 2 | 10
2 | 4 | 6
4 | 5 | 4
5 | 8 | 8
8 | 10 | 5

In the above table, I want to get sum of (1~5) and (5~10) of start and end value assuming that start and end value represents time interval. For example, in the start and end range of (1~5), I want to sum 10, 6, and 4 as those three values reside in the start and end column value of 1~ 5. I thought GROUP BY is a way to do this, but I could not get an answer. Can anyone help with it? thx.

[id10t]

Hi!

[DELETED]

[kyle]

Thanks for the reply, sKwa. I didn't know about "decode", and it seems to be very helpful for general purpose.
You are right, the group by is not possible for a range of values, but I could find out another trick to do that using width_bucket function in Vertica.

In the above example, I can do the following assuming the width among buckets are the same

Code: Select all

kyle=> select * from kyle order by startTime;
 startTime | endTime | value
-----------+---------+-------
         1 |       2 |    10
         2 |       4 |     6
         4 |       5 |     4
         5 |       8 |     8
         8 |      10 |     5
(5 rows)

kyle=> select startTime, width_bucket(startTime, 0, 10, 2) from kyle order by startTime;
 startTime | width_bucket
-----------+--------------
         1 |            1
         2 |            1
         4 |            1
         5 |            2
         8 |            2
(5 rows)

Now I can perform group by based on the width_bucket value

Always thanks for your help, sKwa!!