How can I get the sum of a row using LIMIT-CodePudding

id	reg_No	Subj_id	sub_title	score	Class_id
1	98	23	MATHEMATICS	90	2
2	98	21	ENGLISH LANG	60	2
3	98	24	PHYSICS	78	2
4	98	23	CHEMISTRY	100	2
5	98	21	BIOLOGY	81	2
6	98	24	AGRICULTURE	87	2

I want to select the best SUM(score) of the four(4) subjects including English and mathematics.

It suppose to sum 90 60 100 87 = 337

But, it's summing the entire column

Here is my query

SELECT SUM(score) 
FROM table1 
WHERE reg_no = 98 
  AND class_id=2 
ORDER BY CASE WHEN sub_title IN ('English Language','Mathematics') 
              THEN 0 
              ELSE 1 END, score DESC LIMIT 4

CodePudding user response：

The priority of SELECT is higher than LIMIT, therefore, you have to use a subquery

SELECT sum(score) 
FROM
(
    SELECT *
    FROM tab
    WHERE reg_no = 98 AND class_id=2
    ORDER BY CASE WHEN sub_title IN ('English Language','Mathematics') 
                  THEN 0 
                  ELSE 1 END, score DESC 
    LIMIT 4 
) t

DEMO

CodePudding user response：

An easy way to split into two queries. The first one gets the Math and English score values, and the second one gets the two highest scores from the remaining values.

SQL Server:

With CTE As (
Select Top 2 Score From table1 Where reg_no = 98 And class_id=2 And sub_title Not In ('MATHEMATICS','ENGLISH LANG') 
Order by Score Desc
Union All
Select Score From table1 Where reg_no = 98 And class_id=2 And sub_title In ('MATHEMATICS','ENGLISH LANG')
)
Select Sum(Score)
From CTE

MySQL:

With CTE As (
Select Score
From
(Select Score From table1 Where reg_no = 98 And class_id=2 And sub_title Not In ('MATHEMATICS','ENGLISH LANG')
 Order by Score Desc
 Limit 2) As S
Union All
Select Score From table1 Where reg_no = 98 And class_id=2 And sub_title In ('MATHEMATICS','ENGLISH LANG')
)
Select Sum(Score)
From CTE