How to find the nearest of two numbers average in the given three numbers in SQL query

Question

1.00/5 (2 votes)

See more:

I have a SQL Table name Finalmarks and columns are -

RegdNo	    Subject_code	VAL1	VAL2	VAL3
112023221   HPENG221	     28	     44	    30
112023225   HPMAT333	     10	     20	    30

for first row I need output value is 28+30=29
for second row I need output value is 20+30=25

What I have tried:

I Have tried this following query but it gives wrong output for first row values as (44+30)/2=37 instead of (28+30)/2=29

<pre lang="SQL">SELECT RegdNo, Subject_code,
ROUND(((CASE WHEN VAL1 >= VAL2 AND VAL1 >= VAL3 THEN VAL1
WHEN VAL2 >= VAL1 AND VAL2 >= VAL3 THEN VAL2
ELSE VAL3 END) +
(CASE WHEN (VAL1 >= VAL2 AND VAL1 <= VAL3) OR (VAL1 <= VAL2 AND VAL1 >= VAL3) THEN VAL1
WHEN (VAL2 >= VAL1 AND VAL2 <= VAL3) OR (VAL2 <= VAL1 AND VAL2 >= VAL3) THEN VAL2
ELSE VAL3 END)) / 2, 0) AS FinalMArks
FROM FinalMarks;

Posted 29-Apr-23 7:25am

Sharan Kumar Vaddadi

Updated 5-May-23 9:39am

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2023-04-29T10:20:00

Look at what you are doing: where is it calculating averages?

Your task is to find the pair of values with the largest average: (V1 + V2) / 2, V1 + V3) / 2, or (V2 + V3 ) / 2

Your code doesn't do that - it doesn't work out averages at all, it just compares values, so you get the wrong results.

Have a think about exactly what your homework requires, and read the assignment again. Then try it manually before you jump into code.

Maciej Los · Answer 2 · 2023-05-05T09:39:00

First of all, you need to unpivot data. See: Using PIVOT and UNPIVOT - SQL Server | Microsoft Learn[^]

How?
Take a look at example:

SQL

SELECT RegdNo, Subject_code, AVG(Score) AS AvgScore
FROM
(
  SELECT RegdNo, Subject_code, Descr, Score,
    ROW_NUMBER() OVER(PARTITION BY RegdNo ORDER BY Score) AS RN
  FROM   
  (
   SELECT RegdNo, Subject_code, VAL1, VAL2, VAL3  
   FROM FinalMarks
  ) AS PVT  
  UNPIVOT( Score FOR Descr IN (VAL1, VAL2, VAL3)) AS unpvt
) AS FinQry
WHERE RN IN (1, 2)
GROUP BY RegdNo, Subject_code;

Sample: SQL Server 2022 | db<>fiddle[^]

Result:

RegdNo 	    Subject_code 	AvgScore (due to:)
112023221 	HPENG221     	29       (28, 30)
112023225 	HPMAT333 	    15       (10, 20)

How to find the nearest of two numbers average in the given three numbers in SQL query

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0

How to find the nearest of two numbers average in the given three numbers in SQL query

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us