sql sum column - if/then statement

[phoenixaz]

I want to convert a sql sum function into an if/then statement.

sql = "SELECT SUM (Credits)[moneypaid] from Tablename where date > '1/1/2012' and customerid = '8796'"


if moneypaid = 100 then
response.write  "something"
end if

This obviously is not working and I am doing something wrong by not understanding it. Any help would be appreciated.

Thanks

[gk53]

sql = "SELECT case 
             when SUM (Credits)>100 then 'Less then100'
             when SUM (Credits)<100 then 'More then100'
end as [moneypaid] from Tablename where date > '1/1/2012' and customerid = '8796'"

[phoenixaz]

Thanks for the reply. I have never used select case, when and end as before. Does it stack down the page just like that?

[gk53]

just include that in your page an see (of couse all statement one line or concatenate string

[phoenixaz]

Thanks, got that to work, but not able to create the condition I am looking for.

Does the 'Less then100' or the 'More then100' create a variable?

Because what I want to do is pull that data from the table and if moneypaid >= 100 then I want to do something.

Can you do a simple sql statement (without SUM) and then do a SUM function in the if/then statement?

Thanks for your help.

[gk53]

I need more info
"pull that data from the table and if moneypaid >= 100 then I want to do something", but moneypaid is sum(Credits) ????
and what you mean "I want to do something" get another value, print something on screen?
explain please what you trying to do

[phoenixaz]

Use this sql statement to pull data from:

The field "credits" has previous purchase amounts.

sql = "SELECT SUM (Credits)[moneypaid] from Tablename where date > '1/1/2012' and customerid = '8796'"

if moneypaid >= 100 then
response.write "something"
end if

Also, not sure if I need [moneypaid].

[gk53]

you need something like

sql = "SELECT SUM (Credits) as moneypaid from Tablename where date > '1/1/2012' and customerid = '8796'"
set cn=server.createobject("adodb.connection")
' constr = your connection string
cn.open constr
set rs=server.createobject("adodb.recordset")
rs.open sql, cn
if not rs.eof then
if rs("moneypaid")>=100 then
response.write "something"
else
response.write "something else"
end if
end if

[phoenixaz]

Thanks, that works and I get it!!

I appreciate your help!