Summing up rows

[skyboy]

I have a table that looks like the one below:

Name Score1 Score2 Score3 TOTAL
Dan 71 85 23 ?
Tom 58 84 87 ?
sam 76 65 64 ?

I dont know d command that will dynamically get the total score for each student and put it in the TOTAL Column. The datas are gotten from my database. Pls help me out...Thanks

[jbisono]

Try to calculate that at the database level, and return the total with your result. your statement should looks like

SELECT NAME, SCORE1, SCORE2, SCORE3, SCORE1 + SCORE2 + SCORE3 AS TOTAL
FROM YOURTABLE

[Lusiphur]

Try to calculate that at the database level, and return the total with your result. your statement should looks like

SELECT NAME, SCORE1, SCORE2, SCORE3, SCORE1 + SCORE2 + SCORE3 AS TOTAL
FROM YOURTABLE

Definitely agree with bisono here that your best bet is to sum the totals at the SQL server end and return the value directly. Not only does this cut down on overhead in your local code but it also provides the 'total' results as part of the returned dataset so they are directly 'related' to the other values you are retrieving and easily retrieved on a line by line basis. This is preferable to performing the calculation after the fact on the code-behind or front-end levels as it 'ties' the results to the source more directly (read: less chance for information mismatch).

[skyboy]

Thanks Guys i'm very grateful. It worked. But, i tried finding the average with this code...

SELECT NAME, SCORE1, SCORE2, SCORE3, AVG(SCORE1, SCORE2, SCORE3) AS AVERAGE FROM TABLE1

But it keep bringing the following error

Avg requires only one argument, i replaced the commas (,) with addition (+) sign, it still dosent works. Pls guys i'll be grateful if u can help me out.

[jbisono]

The thing is that the AVG Function is an aggregate one, so that usually means that you have to group by so your statement will end something like this.

SELECT NAME, AVG(SCORE1 + SCORE2 + SCORE3) AS AVERAGE
FROM TABLE1
GROUP BY NAME

[skyboy]

thanks, i just tried it out but it just adds everything up without finding d actual average.

[jbisono]

well remember that the avg function it will find the avg by rows because how many rows its hard to know, but if you want the avg for multiple columns and a specific row, its more straightforward because you know how many columns are so you will end up with something like.

SELECT NAME, ((SCORE1 + SCORE2 + SCORE3) / 3) AS AVERAGE
FROM TABLE1

[crishjeny]

Hi

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

this code shows the use of Summing up rows.
To get more detail about code go this below link.

http://techonthenet.com/sql/sum.php