CODEWITHSUNDEEP
sqlsql-server
Shreyas Sawant
Shreyas Sawant

Reputation: 45

Sub-Query Returning more than one value

I have a requirement where I need to find median of three values, i.e get the middle value which is not the Min and/or Max of the values. When I'm running this patch of code as given below [Providing Static Values], I'm getting the required result. Running this batch of code in a loop for dynamic values is causing the problem.

I tried running this logic individually for the values that returned error and it worked fine, gave me the expected results

DECLARE @MAX FLOAT
DECLARE @final_weight FLOAT
DECLARE @TempMedianTable TABLE (rownum int, weights Float)

INSERT INTO @TempMedianTable 
SELECT ROW_NUMBER() Over (Order By v ASC) ,v 
FROM (Values (10514.7),(1820.03),(884.52)) AS Value(v)

SELECT * FROM @TempMedianTable

SET @MAX = (SELECT max(weights) FROM @TempMedianTable)
PRINT @MAX

SET @final_weight = CEILING(IIF(@MAX > 1000, (SELECT weights FROM @TempMedianTable WHERE rownum = 2), @MAX ))
PRINT @final_weight

The following error is returned for the subquery on running for dynamic values:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SET @final_weight = CEILING(IIF(@MAX > 1000, (SELECT weights FROM @TempMedianTable WHERE rownum = 2), @MAX ))

Upvotes: 1

Views: 328

Answers (2)

Zorkolot
Zorkolot

Reputation: 2027

In your specific example, it's only 3 terms- so you would just choose the middle value. But understand this is not how median is actually calculated.

The median is calculated like so: The set is ordered from least to greatest. If there is an odd number of terms, then the median is the middle term. If there is an even number of terms then the median is the sum of the two middle values divided by two.

If you have SQL Server 2016 or above, you can use PERCENTILE_CONT(0.5) to find the true median. This would apply no matter the number of terms, whether an even or odd number of terms.

https://learn.microsoft.com/en-us/sql/t-sql/functions/percentile-disc-transact-sql?view=sql-server-2016

SELECT DISTINCT PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY [weights]) 
         OVER (PARTITION BY 1) AS [median]
  FROM @TempMedianTable

In this case I gave all values the same partition = 1, since PERCENTILE_CONT requires a partition.

For your example data this would produce output:

median
1820.03

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

If you want the median of three values, why not just do this?

SELECT v.Value
FROM (Values (10514.7),(1820.03),(884.52)) AS Value(v)
ORDER BY Value.v
OFFSET 1 ROW FETCH FIRST 1 ROW ONLY;

Upvotes: 2

Related Questions