15,918,268 members
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
Updated 5-May-23 9:39am

## Solution 1

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.

## Solution 3

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)```