Re: Sum rows based on a column with duplicates

[Martin Davis]

Easy to do, Yao. This actually a very common need when working with
SQL aggregates, and it's not easy to do in standard SQL. In SQL it
usually requires a self-JOIN or something similar (or it may be
possible with window functions) - in any case, it's always a bit of a
brain-twister.

So JEQL makes this easier by adding some new aggregate functions FIRST
and LAST. They simply select a value from a column to be present in
the aggregated result.

In this case, use the statement:

t = select x, avg(y), first(z) from t1 group by x;

The result is:

x:String, col1:Double, col2:Integer
a 20.0 1
b 35.0 4
c 50.0 6
d 60.0 7


On Thu, May 31, 2012 at 2:54 PM, Cui, Yao MEM:EX <Yao...@gov.bc.ca> wrote:
> Hi Martin,
>
> I find myself struggle a bit on the following query. Basically col x is a
> kind of id but not unique. I want to summarize col y (average in this case)
> based on the same x and finally pick up the unique x and one of values for
> z.
>
> /*================================
> Test remove/sum rows base on a column with duplicates
> ================================*/
>
> t1 = select * from table (
> ( "a", 10, 1 )
> ( "a", 20, 2 )
> ( "a", 30, 3 )
> ( "b", 30, 4 )
> ( "b", 40, 5 )
> ( "c", 50, 6 )
> ( "d", 60, 7 )
> ) t(x, y, z);
>
> /*-----------------------------------------
> //     expected output
> a, 20, 1
> b, 35, 5
> c, 50, 6
> d, 60, 7
> ----------------------------------------- */
>
> Can you shed some lights please J
>
> Thanks,
>
> Yao
>

[Martin Davis]

By the way, these aggregate functions are documented here:

http://tsusiatsoftware.net/jeql/libref/aggfun.html