Find SUM of columns with condition in SQL-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

Another way of doing is using HAVING.

Try:

select roll_nr,
       sum(sub1 sub2) as total 
from my_tbl 
group by roll_nr
having total >100;

Demo

CodePudding user response：

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