Click here to Skip to main content
15,918,268 members
Please Sign up or sign in to vote.
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

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.
 
Share this answer
 
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)
 
Share this answer
 

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



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