#1
  1. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30

    sql sum column - if/then statement


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

    Code:
    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
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    Code:
    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'"
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    Thanks for the reply. I have never used select case, when and end as before. Does it stack down the page just like that?
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    just include that in your page an see (of couse all statement one line or concatenate string
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    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.
  10. #6
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    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
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    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].
  14. #8
  15. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    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

    Comments on this post

    • phoenixaz agrees : Works - thanks
  16. #9
  17. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    Thanks, that works and I get it!!

    I appreciate your help!

Similar Threads

  1. Replies: 15
    Last Post: February 7th, 2012, 03:14 PM
  2. Replies: 5
    Last Post: November 9th, 2010, 05:03 AM
  3. Help Using Update Statement for Default Column Value ADO.NET
    By Plisken2101 in forum .NET Development
    Replies: 10
    Last Post: November 13th, 2009, 05:50 AM
  4. Replies: 4
    Last Post: October 26th, 2005, 04:16 AM
  5. Replies: 1
    Last Post: July 30th, 2004, 08:04 AM

IMN logo majestic logo threadwatch logo seochat tools logo