We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Aggregation
  4. Weather Observation Station 20
  5. Discussions

Weather Observation Station 20

Sort by

recency

|

3938 Discussions

|

  • tejaswini_ponna1
     + 0 comments

    Select Round(avg(lat_n),4) From ( Select Lat_n, row_number() over(order by lat_n asc) as RN, Count(*) over() as TR From Station ) as SQ Where RN = CEIL(TR/2) or RN = Floor(TR/2)+1;

  • pralay_g
     + 0 comments
    DECLARE @TotalCount INT = (
    SELECT COUNT(*) 
    FROM STATION
    WHERE [LAT_N] IS NOT NULL
);

WITH SortedNL AS (
    SELECT 
        [LAT_N],
        ROW_NUMBER() OVER(ORDER BY [LAT_N]) AS SortedIndex
    FROM STATION
    WHERE [LAT_N] IS NOT NULL
)
SELECT CAST(
    CASE
        WHEN(@TotalCount % 2 = 0) THEN AVG([LAT_N])
        ELSE MAX([LAT_N])    
    END AS DECIMAL(10,4)
) AS Median
FROM SortedNL
WHERE SortedIndex IN (
    (@TotalCount + 1) / 2,
    (@TotalCount + 2) / 2
);

  • pralay_g
     + 0 comments
    DECLARE @NLCount INT = (
    SELECT COUNT([LAT_N])
    FROM STATION
    WHERE [LAT_N] IS NOT NULL
);

DECLARE @NLArray TABLE (
    [ARR_INDEX] INT IDENTITY(1,1),
    [LAT_N] DECIMAL(10,4)
);

INSERT INTO @NLArray
SELECT [LAT_N]
FROM STATION
WHERE [LAT_N] IS NOT NULL
ORDER BY [LAT_N];

SELECT CAST(
CASE
    WHEN (@NLCount % 2 = 0) THEN (
        SELECT AVG([LAT_N])
        FROM @NLArray
        WHERE [ARR_INDEX] IN (
            @NLCount / 2,
            (@NLCount / 2) + 1
        )
    )
    ELSE (
        SELECT [LAT_N]
        FROM @NLArray
        WHERE [ARR_INDEX] = (@NLCount + 1) / 2
    )
END AS DECIMAL(10,4)) AS Median;

  • awab1355
     + 0 comments

    select round(lat_n,4) from station order by lat_n LIMIT 1 OFFSET 249;

  • manilagupta93
     + 0 comments

    set @tot = (select count(id) from station);

    select case when mod(@tot,2)=0 then (select round(avg(lat_n),4) from (select *,row_number() over (order by lat_n) as rown from station) sub where rown=(@tot/2) or rown=(@tot/2-1)) else (select round(lat_n,4) from (select *,row_number() over (order by lat_n) as rown from station) sub where rown=ceil(@tot/2)) end;