Find SUM of columns with condition in SQL

Question

Roll no. |Sub1|sub2|
1.        |20. |50. |
2.        |80. |90. |

The sample data is given about now I need to find out the roll number where the sum of subject Sub1 and Sub2 is more than 100

I try this but no luck

Select (sub1+sub2) as total
from table 
where total > 100

Answer 1

You have to write the calculation in the where condition as well, because the where condition is processed before the select-statement.

Select (sub1+sub2) as total
from table 
where (sub1+sub2) > 100

Answer 2

It is possible to use alias but outside of query by using CTE:

CREATE TABLE tblName (roll_number INT, sub1 INT, sub2 INT);

INSERT INTO tblName (roll_number, sub1, sub2) VALUES (1 ,50, 20);
INSERT INTO tblName (roll_number, sub1, sub2) VALUES (2, 110, 20);
INSERT INTO tblName (roll_number, sub1, sub2) VALUES (3, 120, 20);
INSERT INTO tblName (roll_number, sub1, sub2) VALUES (4, 30, 20);

WITH cte AS (
    SELECT roll_number AS RollNumber, (sub1 + sub2) AS Total
    FROM tblName
)
SELECT * FROM cte
WHERE Total > 100

Returns:

RollNumber	Total
2	130
3	140

Tested on MySQL 8.0.

Answer 3

your can try this simple query

 WITH 
CTE1 AS (SELECT SUM(`sub1`+`sub2`) AS subject_total,id as roll_num FROM `tbl` group by id ) 
SELECT * FROM CTE1 
WHERE subject_total > 100; 

Demo link