Calculations in the SQL

# Thread: Calculations in the SQL

1. Senior Member
Join Date
Dec 1969
Posts
745

## Calculations in the SQL

I&#039;m trying to do calculations in the SQL Query. I&#039;m able to do some and not others.<BR><BR>Whats happening is I&#039;m adding severals calculations, and if one of the calculations is 0 my total is 0 but I don&#039;t want that I want the total of all including the 0.<BR><BR><BR><BR>Ex: should be: 200+200+0+300+500 = 1200<BR> what I&#039;m getting: 200+200+0+300+500 = 0<BR><BR><BR>thanks<BR><BR><BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
2,225

## Show your SQL statement <eop>

.

3. Senior Member
Join Date
Dec 1969
Posts
745

## RE: Show your SQL statement <eop>

Heres the part of the sql with the calcs. The entire sql is large.<BR><BR>sql = sql & " [auto_rev]/[auto_exp] as au_newrate, ([gl_rev]/[gl_exp])*1000 as gl_newrate, ([wc_rev]/[wc_exp])*1000 as wc_newrate, ([prop_rev]/[prop_exp])*100 as prop_newrate, "<BR> sql = sql & " [auto_rev]-[aup] as au_rpc, ([au_newrate]-[auPrate])*[auto_exp] as auto_rtchng, [au_rpc]-[auto_rtchng] as auto_expchng,"<BR> sql = sql & " [gl_rev]-[glp] as gl_rpc, ([gl_newrate]-[glPrate])*[gl_exp]/1000 as gl_rtchng, [gl_rpc]-[gl_rtchng] as gl_expchng,"<BR> sql = sql & " [wc_rev]-[wcp] as wc_rpc, ([wc_newrate]-[wcPrate])*[wc_exp]/100 as wc_rtchng, [wc_rpc]-[wc_rtchng] as wc_expchng,"<BR> sql = sql & " [prop_rev]-[prp] as prop_rpc, ([prop_newrate]-[prPrate])*[prop_exp]/100 as prop_rtchng, [prop_rpc]-[prop_rtchng] as prop_expchng,"<BR> sql = sql & " [umb_rev]-[xsp] as umb_rpc, [au_rpc]+[gl_rpc]+[wc_rpc]+[prop_rpc]+[umb_rpc] as tot_rpc, ([umb_rev]/[gl_exp])*1000 as umb_newrate,"<BR> sql = sql & " ([umb_newrate]-[xsPrate])*[gl_exp]/1000 as umb_rtchng, [umb_rpc]-[umb_rtchng] as umb_expchng, [umb_rtchng]+[gl_rtchng]+[prop_rtchng]+[wc_rtchng]+[umb_rtchng] as tot_rtchng,"<BR> sql = sql & " [auto_expchng]+[gl_expchng]+[wc_expchng]+[prop_expchng]+[umb_expchng] as tot_expchng, "<BR> sq

4. oli
Senior Member
Join Date
Dec 1969
Posts
3,961

## RE: Show your SQL statement <eop>

Why don&#039;t you perform the final mathematical calculation using ASP. That SQL query looks like a complete nightmare.

5. Senior Member
Join Date
Dec 1969
Posts
745

## RE: Show your SQL statement <eop>

How can I do calcs using ASP?

6. oli
Senior Member
Join Date
Dec 1969
Posts
3,961

## RE: Show your SQL statement <eop>

7. Senior Member
Join Date
Dec 1969
Posts
745

## RE: Show your SQL statement <eop>

How about formating the result?<BR><BR>I tried <BR><BR>response.write(formatNumber(tot),0) and that don&#039;t work.<BR><BR><BR>is there another way I can format the numbers to display like<BR><BR>5,555 instead of 555556565

8. oli
Senior Member
Join Date
Dec 1969
Posts
3,961

## RE: Show your SQL statement <eop>

You need to specify how you want the number to be formatted by using the FormatNumber parameters.<BR><BR>FormatNumber(Expression,NumDigi tsAfterDecimal,IncludeLeadingDigit,UseParensForNeg ativeNumbers,GroupDigits)<BR><BR>&#060;%<BR>MyNum = 555556565<BR>Response.write FormatNumber(MyNum,0,0,0,-1)<BR>%&#062;<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•