Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: sql sum column - if/then statement

Results 1 to 9 of 9
Share This Thread →
  1. #1
    phoenixaz is offline Contributing User
    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
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    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

  3. #3
    phoenixaz is offline Contributing User
    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?

  4. #4
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    Rep Power
    278
    just include that in your page an see (of couse all statement one line or concatenate string

  5. #5
    phoenixaz is offline Contributing User
    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.

  6. #6
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    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

  7. #7
    phoenixaz is offline Contributing User
    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].

  8. #8
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    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

  9. #9
    phoenixaz is offline Contributing User
    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    Thanks, that works and I get it!!

    I appreciate your help!

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates