How to calculate the median

Moderator: NorbertKrupa

Post Reply
atul.pdm@gmail.com
Newbie
Newbie
Posts: 7
Joined: Wed Oct 26, 2016 5:12 pm

How to calculate the median

Post by atul.pdm@gmail.com » 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

Code: Select all

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to calculate the median

Post by JimKnicely » 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:

Code: Select all

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

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”