Number format problems with SUM and AVG

[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("Sel ect 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("Sel ect 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

[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*****@ielear ning.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.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*****@ielearn ing.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("Sel ect 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("Sel ect 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

[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*****@ielear ning.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.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*****@ielearn ing.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("Sel ect 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("Sel ect 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

[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*****@ielearn ing.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*****@ielear ning.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.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*****@ielearn ing.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("Sel ect 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("Sel ect 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

[Willem-Jan Selen]

Alright I solved it with the following

Set rs = db.execute("Sel ect * 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(pr ize,2)

and the averge number by using

set rs = db.execute("Sel ect 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(av eprize2,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*****@ielear ning.com> wrote in message
news:eW******** *****@TK2MSFTNG P12.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*****@ielearn ing.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*****@ielear ning.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.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*****@ielearn ing.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("Sel ect 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("Sel ect 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
>
>

[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("Sel ect * 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(pr ize,2)

and the averge number by using

set rs = db.execute("Sel ect 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(av eprize2,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*****@ielear ning.com> wrote in message
news:eW******** *****@TK2MSFTNG P12.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*****@ielearn ing.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*****@ielear ning.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.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*****@ielearn ing.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("Sel ect 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("Sel ect 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
> >
> >
>
>

[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*****@ielearn ing.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("Sel ect * 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(pr ize,2)

and the averge number by using

set rs = db.execute("Sel ect 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(av eprize2,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*****@ielear ning.com> wrote in message
news:eW******** *****@TK2MSFTNG P12.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*****@ielearn ing.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*****@ielear ning.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.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*****@ielearn ing.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("Sel ect 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("Sel ect 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
> >
> >
>
>