October 20, 2017 at 7:04 am
Hi,
I have student marks table. I am calculating Total marks and Avg marks per student.
How can I get the same output using built in functions using SUM() and AVG() functions ?
CREATE TABLE Student_Marks(
student_id int NULL,
student_name varchar(100) NULL,
s1 int NULL,
s2 int NULL,
s3 int NULL,
s4 int NULL,
s5 int NULL,
s6 int NULL
)
GO
INSERT Student_Marks (student_id, student_name, s1, s2, s3, s4, s5, s6) VALUES (101, 'Adam', 100, 100, 100, 100, 100, 98)
INSERT Student_Marks (student_id, student_name, s1, s2, s3, s4, s5, s6) VALUES (102, 'Smith', 87, 65, 90, 85, 98, 100)
INSERT Student_Marks (student_id, student_name, s1, s2, s3, s4, s5, s6) VALUES (103, 'Jordon', 55, 89, 90, 45, 60, 65)
GO
select
student_id,
student_name,
s1,
s2,
s3,
s4,
s5,
s6,
(s1+s2+s3+s4+s5+s6) as Total,
(s1+s2+s3+s4+s5+s6)/6 as [Avg]
from Student_Marks
Thanks,
Sam
October 20, 2017 at 8:01 am
You already have code that does the job, given your table structure. SUM() and AVG() are designed to operate over any number of rows, and not over some number of columns. Designing a table to hold marks in separate columns is not a particularly good idea for this reason, as tying any given mark to when it was recorded means having to add yet more columns, when the alternative could just be having a student id, a date, and a mark, and then getting the total and the average using SUM() and AVG() is a piece of cake. It's the data structure that is causing you to wonder what to do, as opposed to you not necessarily knowing what to do. Change the data structure. It's not maintainable over time as more and more grades would need to be recorded.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 20, 2017 at 8:57 am
Steve has the right answer here. If you want to use SUM or AVG you need to normalise your data. Instead, consider a table such as:USE Sandbox;
GO
CREATE TABLE StudentMark
(ScoreID int IDENTITY(1,1), --Should have a unique id or something really
StudentID int NOT NULL, --Why was not not null? Are you going to have marks not assigned to a student?
--StudentName varchar(100), --this is going in a different table for true 3NF
ScoreName char(3), --3, incase you have 10 scores
Score int);
CREATE TABLE Student
(StudentID int NOT NULL,
StudentName varchar(100) NOT NULL);
--You should then consider KEYS, as StudentID is a Primary and Foreign Key
GO
INSERT INTO Student
VALUES
(101,'Adam'),(102,'Smith'),(103,'Jordan');
INSERT INTO StudentMark (StudentID, ScoreName, Score)
VALUES
(101,'s01',100),
(101,'s02',100),
(101,'s03',100),
(101,'s04',100),
(101,'s05',100),
(101,'s06',98),
(102,'s01',87),
(102,'s02',65),
(102,'s03',90),
(102,'s04',85),
(102,'s05',98),
(102,'s06',100),
(103,'s01',55),
(103,'s02',89),
(103,'s03',90),
(103,'s04',45),
(103,'s05',60),
(103,'s06',65),
(103,'s07',99); --Intentionally added an extra s07 mark
GO
SELECT S.StudentID, S.StudentName,
SUM(SM.Score) AS TotalScore,
AVG(SM.Score) As AverageScore
FROM Student S
JOIN StudentMark SM ON S.StudentID = SM.StudentID
GROUP BY S.StudentID, S.StudentName;
GO
DROP TABLE Student;
DROP TABLE StudentMark;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2017 at 9:16 am
Thanks "Thom A" for the step - by - step approach of the problem. Didn't get what sgmunson was saying. But now I understood what you and he meant. Thanks a lot for the recommended solution.
I tried below and is working as well.
SELECT
student_id,
student_name,
s1,
s2,
s3,
s4,
s5,
s6,
SUM(c1) as TotalMarks,
AVG(c1) as [AvgMarks]
FROM Student_Marks
CROSS APPLY (values(s1),(s2),(s3),(s4),(s5),(s6)) as derived(c1)
GROUP BY
student_id,
student_name,
s1,
s2,
s3,
s4,
s5,
s6
Thanks,
Sam
October 20, 2017 at 10:09 am
vsamantha35 - Friday, October 20, 2017 9:16 AMThanks "Thom A" for the step - by - step approach of the problem. Didn't get what sgmunson was saying. But now I understood what you and he meant. Thanks a lot for the recommended solution.I tried below and is working as well.
SELECT
student_id,
student_name,
s1,
s2,
s3,
s4,
s5,
s6,
SUM(c1) as TotalMarks,
AVG(c1) as [AvgMarks]
FROM Student_Marks
CROSS APPLY (values(s1),(s2),(s3),(s4),(s5),(s6)) as derived(c1)
GROUP BY
student_id,
student_name,
s1,
s2,
s3,
s4,
s5,
s6Thanks,
Sam
While this may work, in the long run, it's not likely to be sustainable. What happens when someone decides another mark is needed? What happens when they then need to keep a date with each mark? By the time some of those things occur, you could have a lot of data in hand, and things could get really ugly really quickly. If you don't understand the performance implications now, by the time they crop up it could end up being too late...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply