Re: SUM() OVER (ORDER BY..)

29 Apr 2016

      Anthony,

while MonetDB indeed does not support this syntax,
I'm also puzzled what kind of semantic or result you'd expect from this syntax?

    SELECT
        batch_id,
        job_count,
        SUM(job_count) OVER (PARTITION BY duration) as cumjobs,
        SUM(job_count) over () as totjobs,
        duration
    FROM
        test_data ;

The only possible result would IMHO be a 
table with as many rows as the input table test_data has,
columns batch_id, job_count, duration identical to the input,
one additional column cumjobs with the same value, i.e., sum
of all job_count within each duration, replicated for all rows
with same duration,
and one additional column totjobs, with the same value (global
sum of all job_count values) replicated for each row.

If that's what you want, this should do the job:

SELECT
        batch_id,
        job_count,
        cumjobs,
        totjobs,
        duration
FROM
        test_data AS td
        NATURAL JOIN
        (
                SELECT
                        duration,
                        SUM(job_count) AS cumjobs
                FROM
                        test_data
                GROUP BY
                        duration
        ) AS cj
        ,
        (
                SELECT
                        SUM(job_count) AS totjobs
                FROM
                        test_data
        ) AS tj
;

Best,
Stefan

----- On Apr 29, 2016, at 9:46 AM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
...
No, indeed, this syntax is not supported.
On 29 April 2016 at 09:41, Anthony Damico < ajdamico@gmail.com > wrote:
hmm, that gives me the same error..
SELECT mpg, wt, SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, SUM(wt) over () AS
tot_wgt, hp FROM mtcars;
Error in .local(conn, statement, ...) :
Unable to execute statement 'SELECT
mpg,
wt,
SUM(wt) OVER (PARTITION BY hp) AS cum_wgt,
SUM(wt) over () AS tot_wgt,
hp
FROM mtc...'.
Server says 'ParseException:SQLparser:SELECT: function 'sum' not found'.
sorry if i'm doing something silly..
On Fri, Apr 29, 2016 at 3:37 AM, Roberto Cornacchia <
roberto.cornacchia@gmail.com > wrote:
I think what you are looking for is SUM(..) OVER(PARTITION BY ..)
Otherwise where would the groups for the sum come from?
Roberto
On 29 April 2016 at 09:28, Anthony Damico < ajdamico@gmail.com > wrote:
hi, monetdb does not support SUM() OVER commands.. does anyone have a smart
alternative to implement this? thanks
SELECT
batch_id,
job_count,
SUM(job_count) OVER (ORDER BY duration) as cumjobs,
SUM(job_count) over () as totjobs,
duration
FROM
test_data ;
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
-- 
| Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |

Re: SUM() OVER (ORDER BY..)

Stefan Manegold