How Median Function work in Sql Server

Question

In Oracle it works well......

Query for oracle is As Follows

Select   COMPONENT_ID, 
         COMPONENT_NAME, 
         POSITION_ID, 
         POSITION_NAME, 
         CHANNEL_ID, 
         CHANNEL_NAME, 
         Max(METRIC) as METRIC, 
         Max(TTD_ATTN) as TTD_ATTN_Max, 
         Min(TTD_ATTN) as TTD_ATTN_Min, 
         Avg(TTD_ATTN) as TTD_ATTN_Avg, 
         Median(TTD_ATTN) as TTD_ATTN_Med, 
         StdDev(TTD_ATTN) as TTD_ATTN_StdDev, 
         Max(COMPONENT_NO) as COMPONENT_NO,
         DATAMONTH, 
         CHANNEL_ID||'_'||POSITION_ID as CHANNEL_POSITION,    
         CHANNEL_NAME||'_'||POSITION_NAME as CHANNEL_POSITION_NAME 
from     SCNCAAF0461A1894981868ABA.PB36E6D13A82C4609B9488950 
Group By COMPONENT_ID,
         COMPONENT_NAME,
         POSITION_ID,
         POSITION_NAME,
         CHANNEL_ID,
         CHANNEL_NAME,
         DATAMONTH

But for SQL Server,getting error

Select   COMPONENT_ID,
         COMPONENT_NAME,
         POSITION_ID,
         POSITION_NAME,
         CHANNEL_ID,
         CHANNEL_NAME,
         max(METRIC) AS METRIC,
         max(TTD_ATTN) AS TTD_ATTN_Max,
         min(TTD_ATTN) AS TTD_ATTN_Min,
         avg(TTD_ATTN) AS TTD_ATTN_Avg,
         Median(TTD_ATTN) AS TTD_ATTN_Med,
         stdev(TTD_ATTN) AS TTD_ATTN_StdDev,
         max(COMPONENT_NO) AS COMPONENT_NO,
         DATAMONTH,
         ISNULL(CHANNEL_ID, '') + '_' + ISNULL(POSITION_ID, '') AS CHANNEL_POSITION,
         ISNULL(CHANNEL_NAME, '') + '_' + ISNULL(POSITION_NAME, '') AS CHANNEL_POSITION_NAME 
from     SCNCAAF0461A1894981868ABA.PB36E6D13A82C4609B9488950 
Group By COMPONENT_ID,    
         COMPONENT_NAME,
         POSITION_ID,
         POSITION_NAME,
         CHANNEL_ID,
         CHANNEL_NAME,
         DATAMONTH

ERROR:Select statement could not be parsed correctly.output Table cannot be generated.

I think their is problem in Median Function. Can Anybody help?

As far as I know, SQL Server does not support MEDIAN, at least not directly as a function (of course, you can calculate the median somehow using SQL). — Will A, Feb 06 '12 at 13:12
possible duplicate of [Function to Calculate Median in Sql Server](http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server) — Lieven Keersmaekers, Feb 06 '12 at 13:15

score 0 · Answer 1 · edited May 23 '17 at 12:03

0

MEDIAN does not exists in SQL Server - not even in SQL Server 2012 (yet to be RTM'ed).

http://msdn.microsoft.com/en-us/library/ms173454(v=sql.110).aspx

The question of how to calculate median with SQL Server is asked / answered here.

edited May 23 '17 at 12:03

Community

1
1

answered Feb 06 '12 at 13:15

Will A

23,926
4
46
60

How Median Function work in Sql Server

1 Answers1