Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - SUM help
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

SUM help
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
ub3rl337ch3ch View Drop Down
Senior Member
Senior Member
Avatar

Joined: 16 February 2005
Location: Australia
Status: Offline
Points: 341 		Post Options Post Options   Thanks (0) Thanks(0)   Quote ub3rl337ch3ch Quote  Post ReplyReply Direct Link To This Post Topic: SUM help
    Posted: 23 February 2005 at 5:38pm
I'm trying to add the sums of two fields in a database, divide one of the fields by the result, and then write both results to a table. Here's what I have:
 
sql = "SELECT SUM(AwayPoint) AS totalaway FROM Score WHERE AwayID =" & vrec("CLId")
vrec2.Open sql, adoCon
sql = "SELECT SUM(HomePoint) AS totalhome FROM Score WHERE HomeID =" & vrec("CLId")
vrec1.Open sql, adoCon dim totalp, percent, totalh, totala
totalh = vrec1("totalhome")
totala = vrec2("totalaway")
totalp = totalh + totala
percent = totalh/totalp
 
and then a response.write for totalp and precent
 
It doesn't give me any errors, but it does display a grand total of zippo in the table. Could someone please point out where i've gone wrong?
<%Response.write vrec("TName")%>
<%Response.Write totalp%>
<%Response.write percent%>


Edited by -boRg- - 24 February 2005 at 5:48am
Back to Top
dj air View Drop Down
Senior Member
Senior Member
Avatar

Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dj air Quote  Post ReplyReply Direct Link To This Post Posted: 24 February 2005 at 6:11am
what happends when you run the code.?

have you tried CLng() the reord values
Back to Top
ub3rl337ch3ch View Drop Down
Senior Member
Senior Member
Avatar

Joined: 16 February 2005
Location: Australia
Status: Offline
Points: 341 		Post Options Post Options   Thanks (0) Thanks(0)   Quote ub3rl337ch3ch Quote  Post ReplyReply Direct Link To This Post Posted: 27 February 2005 at 6:31pm
cheers, CLng() worked a treat.
Back to Top
ub3rl337ch3ch View Drop Down
Senior Member
Senior Member
Avatar

Joined: 16 February 2005
Location: Australia
Status: Offline
Points: 341 		Post Options Post Options   Thanks (0) Thanks(0)   Quote ub3rl337ch3ch Quote  Post ReplyReply Direct Link To This Post Posted: 27 February 2005 at 7:06pm
ok, now I'm getting an Overflow error (just one word, overflow) on the line near the bottom where I try to divide. here's my updated script
 
sql = "SELECT SUM(AwayPoint) AS totalaway FROM Score WHERE AwayID =" & vrec("CLId")
vrec2.Open sql, adoCon
sql = "SELECT SUM(HomePoint) AS totalhome FROM Score WHERE HomeID =" & vrec("CLId")
vrec1.Open sql, adoCon
totalh = vrec1("totalhome")
if IsNull(totalh) then totalh = 0 end if
totalh = CLng(totalh)
totala = vrec2("totalaway")
if IsNull(totala) then totala = 0 end if
totala = CLng(totala)
totalp = totalh + totala
vrec1.close
vrec2.Close
sql = "SELECT SUM(HomePoint) AS againsthome FROM Score WHERE AwayID =" & vrec("CLId")
vrec2.Open sql, adoCon
sql = "SELECT SUM(AwayPoint) AS againstaway FROM Score WHERE HomeID =" & vrec("CLId")
vrec1.Open sql, adoCon
againsth = vrec2("againsthome")
if IsNull(againsth) then againsth = 0 end if
againsth = CLng(totalh)
againsta = vrec1("againstaway")
if IsNull(againsta) then againsta = 0 end if
againsta = CLng(againsta)
againstp = againsth + againsta
againstp = CLng(againstp)
percent = totalh/againstp <-- overflow error here
percent = CLng(percent)


Edited by ub3rl337ch3ch - 27 February 2005 at 7:07pm
Back to Top
dj air View Drop Down
Senior Member
Senior Member
Avatar

Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dj air Quote  Post ReplyReply Direct Link To This Post Posted: 27 February 2005 at 7:10pm
you dont need to use CLng() on all places its used if you do it when its bought into the application/ out of the database it will be done.
Back to Top
dj air View Drop Down
Senior Member
Senior Member
Avatar

Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dj air Quote  Post ReplyReply Direct Link To This Post Posted: 27 February 2005 at 7:11pm
also may be an idea to put the calulations witin brackets as it makes sure you dont get some errors im not sure about the over flow..

the number is becoming to big i think it means
Back to Top
dpyers View Drop Down
Senior Member
Senior Member


Joined: 12 May 2003
Status: Offline
Points: 3937 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dpyers Quote  Post ReplyReply Direct Link To This Post Posted: 27 February 2005 at 7:29pm
divide by 0 will also cause an overflow

Lead me not into temptation... I know the short cut, follow me.
Back to Top
ub3rl337ch3ch View Drop Down
Senior Member
Senior Member
Avatar

Joined: 16 February 2005
Location: Australia
Status: Offline
Points: 341 		Post Options Post Options   Thanks (0) Thanks(0)   Quote ub3rl337ch3ch Quote  Post ReplyReply Direct Link To This Post Posted: 27 February 2005 at 8:36pm
thanks, div by 0 was the problem... just had no idea what overflow was about. Next problem has cropped up, in that the division seems to round automatically, so that 2/6 = 0, 20/6 = 3, 200/6 = 33, 2000/6 = 333. I've tried format number on both the quotient and the numer/denominator, to no avail. If i try to divide by 10 or 100 later to move the decimal place, it just rounds again. How can i get the script to stop rounding automatically?
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.