15,936,709 members
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
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)```

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

Top Experts
Last 24hrsThis month
 OriginalGriff 30 Richard MacCutchan 10 Pete O'Hanlon 10 Dave Kreskowiak 10
 OriginalGriff 710 Pete O'Hanlon 590 Richard Deeming 475 merano99 215 Dave Kreskowiak 180

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900