Calculating ROWs.....!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Calculating ROWs.....!!!

Author

Topic

cool_moon
Starting Member

26 Posts

Posted - 2003-02-20 : 13:25:05

hi there,how can we SUM the ROWS in a table.e.g

rollno 1st_Sem_GPA 2nd_Sem_GPA 3rd_Sem_GPA Avg_GPA
1 3.4 3.2 3.6 = 3.4
2 3 3.8 3.2 = 3.3

(*here 1st_Sem_GPA mean First Semester)

well i don't know weather it is posib;e to calculate the ROWs.well if it is posible plz tell me how.thank.Take care,God Bless U All.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-20 : 13:53:11

You can add up columns in any query you like:

SELECT A, B, A+B as C
FROM Table

I would suggest storing your data in a different way, however. What if a student has a 4th semester?

Consider this table structure (primary key of rollno and Semester)

rollno, Semester, GPA
1,1,3.4
1,2,3.2
1,3,3.6
2,1,3
2,2,3.8
2,3,3.3

Then you can add semesters as needed without adding columns to your tables. You can also link to a table of semesters with info such as start date, end date, etc. you can include fields like "Credits" and "GradePoints" so that GPA can be calculted on the fly, and also totalled correctly for each student based on how many credits they took per semester.

Then, the answer to your question can be:

SELECT Rollno, SUM(GPA) as TotalGPA, AVG(GPA) as AverageGPA
FROM yourTable
GROUP BY Rollno

- Jeff

edit: added "GROUP BY" to the final query.

Edited by - jsmith8858 on 02/20/2003 17:28:21

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-20 : 16:02:52

And if the student doesn't drop out s/he may be there for many years. Also it looks like your req. would be to have the gpa's per semester on a single row. You can Join the table to itself.

SELECT Year
, Rollno
, s1.GPA as 1stGPA
, s2.GPA as 2ndGPA
, s3.GPA as 3rdGPA
, s4.GPA as 4thGPA
, AvgGPA
FROM Table s1, Table s2, Table s3, Table s4
, (Select Year, Rollno, AVG(GPA) As AvgGPA From Table Group By Year, Rollno) As s5
WHERE s1.Year = s2.Year AND s2.Year = s3.Year AND s4.YEAR = s5.YEAR
AND s1.Rollno = s2.Rollno AND s2.Rollno = s3.Rollno AND s3.Rollno = s4.Rollno AND s4.Rollno = 5
AND s1.Semester = 1 AND s2.Semester = 2 AND s3.Semester = 3 AND s4.Semester = 4

Hope this helps

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources