CODEWITHSUNDEEP
mysqlsql
Arvind
Arvind

Reputation: 9

Find SUM of columns with condition in SQL

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

Upvotes: 0

Views: 198

Answers (3)

Sangat
Sangat

Reputation: 137

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

Upvotes: -1

L0ndl3m
L0ndl3m

Reputation: 139

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.

Upvotes: 1

TheFamousSpy
TheFamousSpy

Reputation: 9

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

Upvotes: 1

Related Questions