The Vertica Database Forums

Posted: **Fri Nov 18, 2016 1:31 pm**

I need median of the (TIMESTAMPDIFF('ss',submission_time,start_time) in below query.
My query giving the syntax error.please suggest

SELECT count(job_number),median(TIMESTAMPDIFF('ss',submission_time,start_time)) as median from acctn;

Posted: **Fri Nov 18, 2016 2:02 pm**

Hi,

The MEDIAN function in Vertica is an Analytic Function and it requires an OVER() clause.

See:
https://my.vertica.com/docs/8.0.x/HTML/ ... alytic.htm

Something like this might work for you:

SELECT count(job_number) OVER() as job_number_count,median(TIMESTAMPDIFF('ss',submission_time,start_time)) OVER() as median from acctn LIMIT 1;

How to calculate the median