Number format problems with SUM and AVG - ASP / Active Server Pages

Willem-Jan Selen

Hie everybody,

I have a problem with the following,

I have a table with a row containing numbers with 2 decimal places.

I want to count the sum of these numbers but my valuw returned is far to
high. I think the SUM string doesn't look at the decimal place.

The same is with the AVG string

Here are the ones I tried :

set rs = db.execute("Select SUM(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing
and

set rs = db.execute("Select AVG(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing

Can anybody help me allong the way?!

Thanks in advance,

Willem-Jan Selen

Jul 19 '05 #1

Subscribe Post Reply

4211

Willem-Jan Selen

Ok here's a copy of my row
the result of the AVG string which I wrote down below is 2.810,29 the SUM
string produces the following 95.550,00 which is the exact total of the
average...

Hope any body can help ?

tblEventInfo ePrize
37,50
35,00
35,00
40,00
22,00
40,00
0,00
35,00
37,50
24,00
17,50
30,00
22,50
11,00
10,00
20,00
23,00
25,50
35,00
40,50
40,00
40,00
47,00
47,00
50,00
22,00
35,00
22,00
22,00
25,00
22,00
0
42,50
0

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...

show examples of your data and the results you are getting.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058791405.289835@cache2...
Hie everybody,

I have a problem with the following,

I have a table with a row containing numbers with 2 decimal places.

I want to count the sum of these numbers but my valuw returned is far to
high. I think the SUM string doesn't look at the decimal place.

The same is with the AVG string

Here are the ones I tried :

set rs = db.execute("Select SUM(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing
and

set rs = db.execute("Select AVG(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing

Can anybody help me allong the way?!

Thanks in advance,

Willem-Jan Selen

Jul 19 '05 #2

Chris Barber

Hmm - I'm not sure how it would handle the ',' in there - that's not a
decimal place - it's a comma.

Maybe you have to explicitly convert the field contents to a decimal(10,2)
etc. to get the correct sum and avg values?

Chris.

"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058808057.405075@cache2...

Ok here's a copy of my row
the result of the AVG string which I wrote down below is 2.810,29 the SUM
string produces the following 95.550,00 which is the exact total of the
average...

Hope any body can help ?

tblEventInfo ePrize
37,50
35,00
35,00
40,00
22,00
40,00
0,00
35,00
37,50
24,00
17,50
30,00
22,50
11,00
10,00
20,00
23,00
25,50
35,00
40,50
40,00
40,00
47,00
47,00
50,00
22,00
35,00
22,00
22,00
25,00
22,00
0
42,50
0

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
show examples of your data and the results you are getting.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058791405.289835@cache2...
Hie everybody,

I have a problem with the following,

I have a table with a row containing numbers with 2 decimal places.

I want to count the sum of these numbers but my valuw returned is far to high. I think the SUM string doesn't look at the decimal place.

The same is with the AVG string

Here are the ones I tried :

set rs = db.execute("Select SUM(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing
and

set rs = db.execute("Select AVG(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing

Can anybody help me allong the way?!

Thanks in advance,

Willem-Jan Selen

Jul 19 '05 #3

Mark Schupp

What is the datatype of the eprize column?
What DBMS are you using?
Is the locale numeric format on the DBMS server the same as the format on
the web-server?

What value do you get for aveprize?
What do you get if you leave out formatnumber and just display the raw
results?

If you execute "select count(eprize) from tblEventInfo" do you get the
expected number of rows?

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058808057.405075@cache2...

Ok here's a copy of my row
the result of the AVG string which I wrote down below is 2.810,29 the SUM
string produces the following 95.550,00 which is the exact total of the
average...

Hope any body can help ?

tblEventInfo ePrize
37,50
35,00
35,00
40,00
22,00
40,00
0,00
35,00
37,50
24,00
17,50
30,00
22,50
11,00
10,00
20,00
23,00
25,50
35,00
40,50
40,00
40,00
47,00
47,00
50,00
22,00
35,00
22,00
22,00
25,00
22,00
0
42,50
0

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
show examples of your data and the results you are getting.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058791405.289835@cache2...
Hie everybody,

I have a problem with the following,

I have a table with a row containing numbers with 2 decimal places.

I want to count the sum of these numbers but my valuw returned is far to high. I think the SUM string doesn't look at the decimal place.

The same is with the AVG string

Here are the ones I tried :

set rs = db.execute("Select SUM(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing
and

set rs = db.execute("Select AVG(ePrize) from tbleventinfo")
if rs.EOF = false then
aveprize = Formatnumber(rs.fields.item(0),2)
else
aveprize = 0
end if
set rs = Nothing

Can anybody help me allong the way?!

Thanks in advance,

Willem-Jan Selen

Jul 19 '05 #4

Willem-Jan Selen

Alright I solved it with the following

Set rs = db.execute("Select * from tblEventinfo where evalid = true")
prize = 0
if rs.EOF = false then
while not rs.EOF
wj = rs("eprize")
prize = prize + wj
rs.moveNext
wend
End If
set rs = Nothing

and displaying the final value as
Formatnumber(prize,2)

and the averge number by using

set rs = db.execute("Select COUNT(eID) from tblEventInfo where evalid =
true")
if rs.EOF = false then
numOfparty = rs.fields.item(0)
else
numOfparty = 0
end if
set rs = Nothing
then dispaying the average
aveprize = prize / numofparty
echo Formatnumber(aveprize2,2)

And I know PRICE is not spelled PRIZE but does it mather in all of the
coding?

Thanx for all the help,
Willem-Jan Selen
"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:eW*************@TK2MSFTNGP12.phx.gbl...

What is the datatype of the eprize column?
What DBMS are you using?
Is the locale numeric format on the DBMS server the same as the format on
the web-server?

What value do you get for aveprize?
What do you get if you leave out formatnumber and just display the raw
results?

If you execute "select count(eprize) from tblEventInfo" do you get the
expected number of rows?

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058808057.405075@cache2...
Ok here's a copy of my row
the result of the AVG string which I wrote down below is 2.810,29 the SUM
string produces the following 95.550,00 which is the exact total of the
average...

Hope any body can help ?

tblEventInfo ePrize
37,50
35,00
35,00
40,00
22,00
40,00
0,00
35,00
37,50
24,00
17,50
30,00
22,50
11,00
10,00
20,00
23,00
25,50
35,00
40,50
40,00
40,00
47,00
47,00
50,00
22,00
35,00
22,00
22,00
25,00
22,00
0
42,50
0

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
show examples of your data and the results you are getting.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058791405.289835@cache2...
> Hie everybody,
>
> I have a problem with the following,
>
> I have a table with a row containing numbers with 2 decimal places.
>
> I want to count the sum of these numbers but my valuw returned is
far to > high. I think the SUM string doesn't look at the decimal place.
>
> The same is with the AVG string
>
> Here are the ones I tried :
>
> set rs = db.execute("Select SUM(ePrize) from tbleventinfo")
> if rs.EOF = false then
> aveprize = Formatnumber(rs.fields.item(0),2)
> else
> aveprize = 0
> end if
> set rs = Nothing
>
>
> and
>
> set rs = db.execute("Select AVG(ePrize) from tbleventinfo")
> if rs.EOF = false then
> aveprize = Formatnumber(rs.fields.item(0),2)
> else
> aveprize = 0
> end if
> set rs = Nothing
>
>
>
> Can anybody help me allong the way?!
>
> Thanks in advance,
>
> Willem-Jan Selen
>
>

Jul 19 '05 #5

Chris Barber

You can do this in one single SQL statement but you will have to handle the
column datatype yourself in the SQL statement. your solution relies on the
fact that the system locale understands the numeric format.

Have a look at CONVERT and CAST in SQL Books Online?

Chris.

"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058825681.293243@cache2...

Alright I solved it with the following

Set rs = db.execute("Select * from tblEventinfo where evalid = true")
prize = 0
if rs.EOF = false then
while not rs.EOF
wj = rs("eprize")
prize = prize + wj
rs.moveNext
wend
End If
set rs = Nothing

and displaying the final value as
Formatnumber(prize,2)

and the averge number by using

set rs = db.execute("Select COUNT(eID) from tblEventInfo where evalid = true")
if rs.EOF = false then
numOfparty = rs.fields.item(0)
else
numOfparty = 0
end if
set rs = Nothing
then dispaying the average
aveprize = prize / numofparty
echo Formatnumber(aveprize2,2)

And I know PRICE is not spelled PRIZE but does it mather in all of the
coding?

Thanx for all the help,
Willem-Jan Selen
"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:eW*************@TK2MSFTNGP12.phx.gbl...
What is the datatype of the eprize column?
What DBMS are you using?
Is the locale numeric format on the DBMS server the same as the format on
the web-server?

What value do you get for aveprize?
What do you get if you leave out formatnumber and just display the raw
results?

If you execute "select count(eprize) from tblEventInfo" do you get the
expected number of rows?

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058808057.405075@cache2...
Ok here's a copy of my row
the result of the AVG string which I wrote down below is 2.810,29 the

SUM string produces the following 95.550,00 which is the exact total of the average...

Hope any body can help ?

tblEventInfo ePrize
37,50
35,00
35,00
40,00
22,00
40,00
0,00
35,00
37,50
24,00
17,50
30,00
22,50
11,00
10,00
20,00
23,00
25,50
35,00
40,50
40,00
40,00
47,00
47,00
50,00
22,00
35,00
22,00
22,00
25,00
22,00
0
42,50
0

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
> show examples of your data and the results you are getting.
>
> --
> Mark Schupp
> --
> Head of Development
> Integrity eLearning
> Online Learning Solutions Provider
> ms*****@ielearning.com
> http://www.ielearning.com
> 714.637.9480 x17
>
>
> "Willem-Jan Selen" <ws****@hotmail.com> wrote in message
> news:1058791405.289835@cache2...
> > Hie everybody,
> >
> > I have a problem with the following,
> >
> > I have a table with a row containing numbers with 2 decimal places. > >
> > I want to count the sum of these numbers but my valuw returned is

far
to
> > high. I think the SUM string doesn't look at the decimal place.
> >
> > The same is with the AVG string
> >
> > Here are the ones I tried :
> >
> > set rs = db.execute("Select SUM(ePrize) from tbleventinfo")
> > if rs.EOF = false then
> > aveprize = Formatnumber(rs.fields.item(0),2)
> > else
> > aveprize = 0
> > end if
> > set rs = Nothing
> >
> >
> > and
> >
> > set rs = db.execute("Select AVG(ePrize) from tbleventinfo")
> > if rs.EOF = false then
> > aveprize = Formatnumber(rs.fields.item(0),2)
> > else
> > aveprize = 0
> > end if
> > set rs = Nothing
> >
> >
> >
> > Can anybody help me allong the way?!
> >
> > Thanks in advance,
> >
> > Willem-Jan Selen
> >
> >
>
>

Jul 19 '05 #6

Mark Schupp

Well, I guess that will work, but I wouldn't trust my DBMS to do anything
right if it didn't give me an accurate value for the SUM of a column!!!!

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058825681.293243@cache2...

Alright I solved it with the following

Set rs = db.execute("Select * from tblEventinfo where evalid = true")
prize = 0
if rs.EOF = false then
while not rs.EOF
wj = rs("eprize")
prize = prize + wj
rs.moveNext
wend
End If
set rs = Nothing

and displaying the final value as
Formatnumber(prize,2)

and the averge number by using

set rs = db.execute("Select COUNT(eID) from tblEventInfo where evalid = true")
if rs.EOF = false then
numOfparty = rs.fields.item(0)
else
numOfparty = 0
end if
set rs = Nothing
then dispaying the average
aveprize = prize / numofparty
echo Formatnumber(aveprize2,2)

And I know PRICE is not spelled PRIZE but does it mather in all of the
coding?

Thanx for all the help,
Willem-Jan Selen
"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:eW*************@TK2MSFTNGP12.phx.gbl...
What is the datatype of the eprize column?
What DBMS are you using?
Is the locale numeric format on the DBMS server the same as the format on
the web-server?

What value do you get for aveprize?
What do you get if you leave out formatnumber and just display the raw
results?

If you execute "select count(eprize) from tblEventInfo" do you get the
expected number of rows?

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
ms*****@ielearning.com
http://www.ielearning.com
714.637.9480 x17
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058808057.405075@cache2...
Ok here's a copy of my row
the result of the AVG string which I wrote down below is 2.810,29 the

SUM string produces the following 95.550,00 which is the exact total of the average...

Hope any body can help ?

tblEventInfo ePrize
37,50
35,00
35,00
40,00
22,00
40,00
0,00
35,00
37,50
24,00
17,50
30,00
22,50
11,00
10,00
20,00
23,00
25,50
35,00
40,50
40,00
40,00
47,00
47,00
50,00
22,00
35,00
22,00
22,00
25,00
22,00
0
42,50
0

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
> show examples of your data and the results you are getting.
>
> --
> Mark Schupp
> --
> Head of Development
> Integrity eLearning
> Online Learning Solutions Provider
> ms*****@ielearning.com
> http://www.ielearning.com
> 714.637.9480 x17
>
>
> "Willem-Jan Selen" <ws****@hotmail.com> wrote in message
> news:1058791405.289835@cache2...
> > Hie everybody,
> >
> > I have a problem with the following,
> >
> > I have a table with a row containing numbers with 2 decimal places. > >
> > I want to count the sum of these numbers but my valuw returned is

far
to
> > high. I think the SUM string doesn't look at the decimal place.
> >
> > The same is with the AVG string
> >
> > Here are the ones I tried :
> >
> > set rs = db.execute("Select SUM(ePrize) from tbleventinfo")
> > if rs.EOF = false then
> > aveprize = Formatnumber(rs.fields.item(0),2)
> > else
> > aveprize = 0
> > end if
> > set rs = Nothing
> >
> >
> > and
> >
> > set rs = db.execute("Select AVG(ePrize) from tbleventinfo")
> > if rs.EOF = false then
> > aveprize = Formatnumber(rs.fields.item(0),2)
> > else
> > aveprize = 0
> > end if
> > set rs = Nothing
> >
> >
> >
> > Can anybody help me allong the way?!
> >
> > Thanks in advance,
> >
> > Willem-Jan Selen
> >
> >
>
>

Jul 19 '05 #7

Similar topics

custom Number operator

by: netclectic | last post by:

Hi folks, i've searched and searched and can't find any example of what i'm trying to do. Essentially (i think) i need to add a new operator to Number. I'm using eval to evaluate expressions...

Javascript

How do I get the full month name and not just the number?

by: jm | last post by:

I am trying to use datepart to get the real name of the month like "April" or "APR" not just "4." I could not find it in the documentation. Sorry. Thank you.

Microsoft Access / VBA

International Number Formatting

by: Steve Peterson | last post by:

Hi I have an app where I have to deal with both Spanish & American formatting. I have a string that represents a number that I need to convert to Int32 before I enter it in the database. The...

Visual Basic .NET

Date from Number of Days

by: james | last post by:

I have a problem that at first glance seems not that hard to figure out. But, so far, the answer has escaped me. I have an old database file that has the date(s) stored in it as number of days. An...

Visual Basic .NET

How to get recordcount or number of records when using linked tables

by: Robert | last post by:

How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...

Microsoft Access / VBA

Format Number with thousands comma but no decimal places

by: Ntl News Group | last post by:

I have a text box that contains the number 1234, I want the value form the text box to display in a second text box so I used: = The problems is that this displays 1234 and I want it to display...

Microsoft Access / VBA

Problems With X Scale Number Format In Chart

by: Wayne | last post by:

I am charting data from a query that consists of a Date/Time field and a Number field. The Date/Time field is the x scale on my chart. Sometimes data is collected from different times during the...

Microsoft Access / VBA

Setting General Number Format in vba

by: Dixie | last post by:

I am appending some new fields to a table in vba and when I append a number field with is a byte, it does not inherit any format. I want it to be the General Number format, but it is blank. I...

Microsoft Access / VBA

Number format change to text

by: michaelw118 | last post by:

Hi, I hope someone can give me some solutions to these problems I faced. I imported some data to Excel. Then in Access, I table-linked to this file. I have two fields which in Excel is in...

Microsoft Access / VBA

Wordpress or something else?

by: Faith0G | last post by:

I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

Content Management Systems

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

General

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware