470,612 Members | 2,286 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,612 developers. It's quick & easy.

Number format problems with SUM and AVG

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
6 4100
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
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
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
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
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by netclectic | last post: by
2 posts views Thread by Steve Peterson | last post: by
29 posts views Thread by james | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.