How do I calculate a Column Total? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

How do I calculate a Column Total?

Author

Topic

dai.hop
Starting Member

3 Posts

Posted - 2006-04-11 : 06:12:49

Howdy all,

I'm trying to write a piece of .asp code to query my database and return the total value of a column - a total score.

Here is my code:

<%

'declare SQL statement that will query your database
sql = "SELECT SUM(Score) as 'TotalScore' FROM AssessmentItems"

'create ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Repository.mdb")

'Open the connection to the database
connection.Open(sConnString)

'Open the recordset object executing the SQL
recordset.Open sql, connection, 3, 1

'Acquire results
Dim total_score
total_score = recordset.fields("TotalScore").value

'Output value of total_score
Response.Write(total_score)

'close the recordset and connection objects and free up resources
recordset.Close
Set recordset=Nothing
connection.close
Set connection=Nothing

%>

It currently doesn't work - is my SQL statement the problem?

Many Thanks,

dai.hop

:)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-11 : 06:23:17

U should not put quotes sorrounding the table name or a field name.
U can put Square Brackets or just leave it as it is if the field name (or alias) is a single word or a non-reserved word

SELECT SUM(Score) as [TotalScore] FROM AssessmentItems

U can debug this by copy-pasting the query in Query pane of Access and executing it.

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 06:49:47

>>It currently doesn't work - is my SQL statement the problem?

Did you get error?
Did you get wrong result?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources