Sum of multiple columns values in a row

With help of the following simple query you can find sum of multiple columns values in a row.

In the marks table below we have roll number and marks of each student in physics, chemistry and maths. Now we wish to find sum of marks obtained by each student.

rollno	physics	chemistry	maths
1	29	31	50
2	52	69	72
3	33	35	70
4	47	41	93
5	52	48	94
6	84	64	65
7	93	83	45
8	55	27	91
9	66	35	31
10	89	44	99
11	62	28	94
12	29	86	89
13	46	93	60
14	59	77	66
15	77	85	83

The query and output will be:

SELECT rollno, (physics + chemistry + maths) 
AS total FROM marks

1 2	SELECT rollno, (physics + chemistry + maths) AS total FROM marks

rollno	total
1	110
2	193
3	138
4	181
5	194
6	213
7	221
8	173
9	132
10	232
11	184
12	204
13	199
14	202
15	245

If you wish to get sum of multiple columns values in all rows or sum of marks of all students in all subjects, then

SELECT SUM(physics + chemistry + maths)
AS total FROM marks

1 2	SELECT SUM(physics + chemistry + maths) AS total FROM marks

total
2821

And to obtain all rows from marks table with an extra column as sum of multiple columns values in that row as total:

SELECT rollno, physics, chemistry, maths, 
SUM(physics + chemistry + maths)
AS total FROM marks GROUP BY rollno

SELECT rollno, physics, chemistry, maths,

SUM(physics + chemistry + maths)

AS total FROM marks GROUP BY rollno

rollno	physics	chemistry	maths	total
1	29	31	50	110
2	52	69	72	193
3	33	35	70	138
4	47	41	93	181
5	52	48	94	194
6	84	64	65	213
7	93	83	45	221
8	55	27	91	173
9	66	35	31	132
10	89	44	99	232
11	62	28	94	184
12	29	86	89	204
13	46	93	60	199
14	59	77	66	202
15	77	85	83	245

rollno	physics	chemistry	maths
1	29	31	50
2	52	69	72
3	33	35	70
4	47	41	93
5	52	48	94
6	84	64	65
7	93	83	45
8	55	27	91
9	66	35	31
10	89	44	99
11	62	28	94
12	29	86	89
13	46	93	60
14	59	77	66
15	77	85	83

rollno	total
1	110
2	193
3	138
4	181
5	194
6	213
7	221
8	173
9	132
10	232
11	184
12	204
13	199
14	202
15	245

rollno	physics	chemistry	maths	total
1	29	31	50	110
2	52	69	72	193
3	33	35	70	138
4	47	41	93	181
5	52	48	94	194
6	84	64	65	213
7	93	83	45	221
8	55	27	91	173
9	66	35	31	132
10	89	44	99	232
11	62	28	94	184
12	29	86	89	204
13	46	93	60	199
14	59	77	66	202
15	77	85	83	245

rollno	physics	chemistry	maths
1	29	31	50
2	52	69	72
3	33	35	70
4	47	41	93
5	52	48	94
6	84	64	65
7	93	83	45
8	55	27	91
9	66	35	31
10	89	44	99
11	62	28	94
12	29	86	89
13	46	93	60
14	59	77	66
15	77	85	83

rollno	total
1	110
2	193
3	138
4	181
5	194
6	213
7	221
8	173
9	132
10	232
11	184
12	204
13	199
14	202
15	245

rollno	physics	chemistry	maths	total
1	29	31	50	110
2	52	69	72	193
3	33	35	70	138
4	47	41	93	181
5	52	48	94	194
6	84	64	65	213
7	93	83	45	221
8	55	27	91	173
9	66	35	31	132
10	89	44	99	232
11	62	28	94	184
12	29	86	89	204
13	46	93	60	199
14	59	77	66	202
15	77	85	83	245

Leave a Reply Cancel reply

rollno	physics	chemistry	maths
1	29	31	50
2	52	69	72
3	33	35	70
4	47	41	93
5	52	48	94
6	84	64	65
7	93	83	45
8	55	27	91
9	66	35	31
10	89	44	99
11	62	28	94
12	29	86	89
13	46	93	60
14	59	77	66
15	77	85	83

rollno	total
1	110
2	193
3	138
4	181
5	194
6	213
7	221
8	173
9	132
10	232
11	184
12	204
13	199
14	202
15	245

rollno	physics	chemistry	maths	total
1	29	31	50	110
2	52	69	72	193
3	33	35	70	138
4	47	41	93	181
5	52	48	94	194
6	84	64	65	213
7	93	83	45	221
8	55	27	91	173
9	66	35	31	132
10	89	44	99	232
11	62	28	94	184
12	29	86	89	204
13	46	93	60	199
14	59	77	66	202
15	77	85	83	245