CODEWITHSUNDEEP
mysqlsql
Sudeepta Roy
Sudeepta Roy

Reputation: 31

How to write SQL query to find out sum of highest 4 columns out of 5 columns?

I’ve a below table

ClassTestMark :

| StudentId | SubjectCode | ClassTest_1 | ClassTest_2 | ClassTest_3 | ClassTest_4 | ClassTest_5 | 
|-----------+-------------+-------------+-------------+-------------+-------------+-------------|
| 070451    | Ch-143      |          10 |          15 |          10 |          12 |          14 |

I want to Query result like below :

| StudentId | SubjectCode | Total |
|-----------+-------------+-------|
| 070451    | Ch-143      |   51  |

Upvotes: 2

Views: 289

Answers (1)

Arif
Arif

Reputation: 6478

You can use a technique : First of all, add all values and then deduct lowest value from that result.

bestOf4 = (ClassTest_1+ ClassTest_2+ ClassTest_3+ ClassTest_4+ ClassTest_5) - smallest column value of(ClassTest_1, ClassTest_2, ClassTest_3, ClassTest_4, ClassTest_5)

SQL :

SELECT(`ClassTest_1` + `ClassTest_2` + `ClassTest_3` + `ClassTest_4` + `ClassTest_5` - least( `ClassTest_1` , `ClassTest_2` , `ClassTest_3` , `ClassTest_4` , `ClassTest_5`)
)AS Total
FROM ClassTestMark

Upvotes: 8

Related Questions