SQL SUM FUNCTION

[AGNEW2PRG]

Hi All,

I am trying to select muliple rows and sum the field "GBP" as a TOTAL Value.

The code below is what I have so far. Is this right? i am unable to output the total value of the GBP field..

Any assistance will be most appreciated.

Regards

<------- CODE ------------->

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("cos.mdb")
Set rsAddExpense = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT *,SUM(GBP) as TOTAL FROM TBLEXPENSES;"
Response.Write (Total)

 

[Chopstik]

Briefly, do not use * in your SELECT statement. If you want all of the fields, list them individually. Also, if you are going to use the SUM function, then you will also need to group everything else that you are returning in your recordset.

Otherwise, I'm not sure what your issue may be since you did not show an error message or otherwise indicate the problem (other than "unable to output the total value").

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[gmmastros]

Alternatively, if you want the SUM of EVERY record in the table, you don't need to use group by.

Select Sum(GBP) As Total from tblExpenses

If you want the sum of expenses for each 'something', then you need to use group by.

[tt][blue]
Select Name, Sum(GBP) As Total
From tblExpenses
Group By Name
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AGNEW2PRG]

Thanks,

I will givi that a try...

Regards