Sum of multiple columns SQL

Question

I have student mark data set as below

RegNo Subj1 Mark1 Subj2 Mark2 Subj3 Mark3 Subj4 Mark4

Where Subj is the subject name and the Mark given.

The data is such that, the same subject may be in any of the columns for different students. Eg. Student1 may have chosen Eng as Subj1 where as Student2 may have chosen as Subj2.

Now I require the Average of each subject. How do I find?

You should normalize your data model, then your problem is reduced to a very simple and basic group by aggregate query. — user330315
– user330315, Commented Jun 16, 2015 at 10:03

Raging Bull · Accepted Answer · 2015-06-16 09:53:32Z

8

Your should fix your data structure. Normally, when you have columns that contains the same data and are only distinguished by numbers at the end, you have a bad structure. You want a table that looks like:

Regno Subj Mark

with one row per student and subj. This is called a junction table, and part of the process of normalizing the data.

You can create one on the fly for this query, but you should really fix the data structure:

select subj, avg(mark)
from ((select regno, subj1 as subj, mark1 as mark from studentmark) union all
      (select regno, subj2 as subj, mark2 as mark from studentmark) union all
      (select regno, subj3 as subj, mark3 as mark from studentmark) union all
      (select regno, subj4 as subj, mark4 as mark from studentmark)
     ) sm
group by sub;

Sample in SQL Fiddle.

edited Jun 16, 2015 at 9:53

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

answered Jun 16, 2015 at 9:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raging Bull Over a year ago

Took the liberty to add the fiddle demo.

Lorenzo Gatti · Accepted Answer · 2015-06-16 09:58:20Z

1

You can compute a UNION of the four cases of the subject being in each of the four columns. For example:

SELECT RegNo, Subj1 as Subj, Mark1 as Mark from NormalizeMePlease where Subj1='SQL basics'
UNION
SELECT RegNo, Subj2 as Subj, Mark2 as Mark from NormalizeMePlease where Subj2='SQL basics'
UNION
SELECT RegNo, Subj3 as Subj, Mark3 as Mark from NormalizeMePlease where Subj3='SQL basics'
UNION
SELECT RegNo, Subj4 as Subj, Mark4 as Mark from NormalizeMePlease where Subj4='SQL basics'

It could be turned into a view for convenience. Selecting the average or anything else from properly structured data should be easy.

answered Jun 16, 2015 at 9:58

Lorenzo Gatti

1,2901 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

Sum of multiple columns SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related