Just add the column to your SELECT clause like I did with the first sum. You would benefit greatly by getting a book on SQL. You might be amazed by what can be done.
There is more than one way to do this, but my suggestion puts all the processing (the summing) on the database instead of on ColdFusion.
A little basic SQL: When returning information from the database (SELECT statement), you specify the columns that you want returned in the SELECT clause. Each column is separated from the next with a comma. These columns correspond to the fields in the table. You can give the column a different name than the name of the field it represents (this is called an "alias"
. A column does not have to represent a field in the table. It can be a sort of "virtual" field, if you will. Example,
SELECT "Member:", first_name, last_name
....
returns three fields. The first field's data is constant and has the text "Member:" on every record. The other two fields return different data for each record (as I'm sure you are familliar with). The point here is that column 1's data is constant and does not come from the database. The next point is that a column's data can be the result of some arithmetic operation:
SELECT count(last_name) as num_employees
FROM employees
WHERE gender = 'F'
The count() function returns the count of the number of records that satisfy the where clause. Since the column does not have a name (it doesn't exist in the database), we have to give it an ALIAS, num_employees. When you use the results of this query in ColdFusion, you would refer to the field as 'num_employees'. If you use count(), you will get back only one record. Finally, we come to your example,
SELECT exam1, exam2, exam3, exam1+exam2+exam3 as total_1
...
This select clause returns 4 columns called exam1, exam2, exam3, and total_1. Notice that we used arithmitic to sum three fields, and by placing the summation in the select clause, we created a 4th field. Since this 4th field doesn't really exist in the table, we have to give it an alias.
So, now you can answer your own question. Hint:
SELECT exam1, exam2, exam1 + exam2 as total_1, exam3, exam4, exam3 + exam4 as total_2
I mentioned that there are other ways of doing this. Suppose that your SELECT clause was:
SELECT exam1, exam2, exam3, exam4
In order to get the totals, you would have to write ColdFusion code to sum the appropriate fields. For example,
Code:
<cfquery name="scores" datasource= ....>
SELECT s.student_name, t.exam1, t.exam2, t.exam3, t.exam4
FROM students s, test_scores t
WHERE s.student_id = t.student_id
ORDER BY student_name
</cfquery>
<table border>
<th>Name</th>
<th>Exam 1</th>
<th>Exam 2</th>
<th>Total</th>
<th>Exam 3</th>
<th>Exam 4</th>
<th>Total</th>
<cfoutput query="scores">
<cfset total_1 = exam1 + exam2>
<cfset total_2 = exam2 + exam4>
<tr>
<td>#student_name#</td>
<th>#exam1#</th>
<th>#exam2#</th>
<td>#total_1#</td>
<th>#exam3#</th>
<th>#exam4#</th>
<td>#total_2#</td>
</tr>
</cfoutput>
</table>
By doing the processing in the databse, you speed up your application.
Finally, a comment: The design of your database is not good. You have repeating values represented as fields in your table. This means that you must modify the database structure when the number of exams changes. What happens next semester when there are 6 exams? You must add two fields, and EVEN WORSE, you must modify your code to accomodate the additional fields. This makes your code difficult to maintain and adds the great possiblity that you will introduce errors when you modify it. You should design your database so that you will not have to modify either your database structure or your code.
What you need to do to accomplish this is normalize your database. For your example, you would have these tables:
Code:
students
------------
student_id
firstname
lastname
...
exams
--------------
exam_id
exam_name
exam_date
exam_group
scores
------------
student_id
exam_id
score
You would set relationships between these tables. You retrieve results like this:
[code]
<cfquery name="scores" datasource="#mydatasource#">
SELECT s.student_id, s.firstname, s.lastname, e.exam_name, e.exam_date, sc.score
FROM students s, exams e, scores sc
WHERE s.student_id = sc.student_id
AND sc.exam_id = e.exam_id
ORDER BY s.lastname, s.firstname, e.exam_date
</cfquery>
<cfquery name="all_exams" ...>
SELECT exam_name, exam_date
FROM exams
ORDER BY exam_date
</cfquery>
Then you draw your table like this:
<table border>
<th>Name</th>
<cfoutput query="all_exams">
<th>#exam_name#</th>
</cfoutput>
<th>Total</th>
<cfoutput query="scores" datasource="#mydatasource#" group="#student_id#">
<cfset student_total = 0>
<tr>
<td>#lastname#, #firstname#</td>
<cfoutput>
<td>#score#</td>
<cfset student_total = student_total + score>
</cfoutput>
<td>#student_total#</td>
</tr>
</cfoutput>
</table>
[code]
The above example doesn't accomodate your need to produce intermediate or sub totals. But, it can (and should) be done using data in a table. You could add a field to the exams tables (as I've shown above) that would specify which group the exam belonged to and use that value in your big query ("score") with the 'GROUP BY' clause. Then when drawing the table, your code can figure out which columns should be "Total" and act accordingly. You will notice that I actually did the summing in ColdFusion, contrary to my earlier advice. There is probably a way to do it in the database and still get all the other things that I wanted out of the database, but I didn't try to figure it out. Sometimes, it's better to get the application working and then see if optimization is worthwhile. (It may cost much, much more to pay a programmer to optimize than the optimization is worth.) The main point is that you should design everything so that you do not have to change either your code or your database design when the number of exams, students or subtotals changes.
