Chris
Mon Jul 21 18:02:03 CDT 2003
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" <wselen@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" <mschupp@ielearning.com> wrote in message
> news:eWrQLK9TDHA.556@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
> > mschupp@ielearning.com
> >
http://www.ielearning.com
> > 714.637.9480 x17
> >
> >
> > "Willem-Jan Selen" <wselen@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" <mschupp@ielearning.com> wrote in message
> > > news:edvICN6TDHA.1364@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
> > > > mschupp@ielearning.com
> > > >
http://www.ielearning.com
> > > > 714.637.9480 x17
> > > >
> > > >
> > > > "Willem-Jan Selen" <wselen@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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>