How to sum values in a table with a condition that compares columns?

HeinzWaescher — Tue, 26 Apr 2016 16:01:20 GMT

Hi,

I've got a table like this

ts1 | ts2 | count  | id
1461347440 | 1461347448 | 5 | 1234
1461347459 | 1461347452 | 10 |  1234
1461347455 | 1461347459 | 10 |  7899

I would like to sum "count" if ts1<=ts2, for each id. My approach looks like this, but it seems to fail because of the field comparison

 | eventstats sum(eval(if(ts1<ts2, count, null()))) AS sum BY Id

Can you help me to get the wanted calculation?

Thanks in advance
Heinz

Re: How to sum values in a table with a condition that compares columns?

woodcock — Tue, 26 Apr 2016 16:13:28 GMT

Whenever you try to add null, you will trash your field so use 0 instead:

| eventstats sum(eval(if(ts1<ts2, count, 0))) AS sum BY Id