CODEWITHSUNDEEP
sqlsql-servermedian
user3115933
user3115933

Reputation: 4443

How can I exclude zeroes in my Median calculation in this specific context?

I am using SQL Server 2014 and I have the following T-SQL query to calculate Median values. However, I want my Median syntax (Percentile_cont) to exclude zeroes when performing the calculation. How can I do this?

CREATE TABLE #MyTempTable (Id int, Market nvarchar(50), [F&B] numeric (19,4) , SPA numeric (19,4))

INSERT INTO #MyTempTable
VALUES
(200, 'UK', 200, 0),
(210, 'UK', 150, 300),
(236, 'FRANCE', 60, 450),
(237, 'FRANCE', 120, 0),
(265, 'UK', 400, 100),
(270, 'FRANCE', 0, 140)

SELECT 
DISTINCT [Market],    
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ([F&B])) OVER (PARTITION BY [Market]) AS 'MEDIAN_F&B',
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ([SPA])) OVER (PARTITION BY [Market]) AS 'MEDIAN_SPA'

FROM #MyTempTable

Current output:

Market  MEDIAN_F&B  MEDIAN_SPA
FRANCE    60            140
UK       200            100

Expected OutPut:

expected ouput

Upvotes: 1

Views: 1861

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Just use conditions:

SELECT DISTINCT [Market],  
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY NULLIF([F&B], 0)) OVER (PARTITION BY [Market]) AS [MEDIAN_F&B],
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY NULLIF([SPA], 0)) OVER (PARTITION BY [Market] AS [MEDIAN_SPA]
FROM #MyTempTable  ;

The PERCENTILE functions ignore NULL values, so this should be the simplest method.

Upvotes: 2

Serg
Serg

Reputation: 22811

Try

SELECT [Market], [MEDIAN_F&B], [MEDIAN_SPA]
FROM 
    (SELECT 
    DISTINCT [Market], CASE [F&B] WHEN 0 THEN 0 ELSE 1 END + CASE [SPA] WHEN 0 THEN 0 ELSE 1 END g,   
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ([F&B])) OVER (PARTITION BY [Market], CASE [F&B] WHEN 0 THEN 0 ELSE 1 END ) AS [MEDIAN_F&B],
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ([SPA])) OVER (PARTITION BY [Market], CASE [SPA] WHEN 0 THEN 0 ELSE 1 END ) AS [MEDIAN_SPA]
    FROM #MyTempTable    
) t
WHERE g=2

Upvotes: 1

Related Questions