Post reply

Column with data type varchar(100).I Need the sum of values of that column if the values are 5,6,7,9,1 etc.

  • June 19, 2011 at 12:27 pm

    #238191

    I have a Column with data type varchar(100).

    In that I have values like 5,6,7,9,1 etc(each values separated with coma)

    Here the question is that I need to make a sum of it

    For eg:- I have 5,6,7,9,1 in that column.

    The result I am expecting is 5+6+7+9+1 = 28 in a new column in the same table.

    Thanks in Advance.

  • June 19, 2011 at 1:01 pm

    #1340890

    try this for starters,

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Then all you need is a sum


    Clear Sky SQL
    My Blog[/url]

  • June 19, 2011 at 1:12 pm

    #1340893

    Added to the above post.

    I am using sql server 2005 and in that one table with values 5,6,7,9,1.

    Note:- Feild data type is varchar.

    Just I need the sum(5,6,7,9,1) = 28 as a result in the same table as a new column.

    Thanks,

    suman

  • June 19, 2011 at 3:39 pm

    #1340907

    Please have a look at what Dave posted. A scalable solution is there for the taking. Hint: look for the function DelimitedSplit8K.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 19, 2011 at 5:44 pm

    #1340921

    mcasuman2004 (6/19/2011)

    Added to the above post.

    I am using sql server 2005 and in that one table with values 5,6,7,9,1.

    Note:- Feild data type is varchar.

    Just I need the sum(5,6,7,9,1) = 28 as a result in the same table as a new column.

    Thanks,

    suman

    What people are suggesting by pointing you to the link that Dave provided is that you must first split out the elements so that you can treat them as separate integers and then add them up. If you'd provide some readily consumable data (see the first link in my signature below), I'm pretty sure that someone would be happy to demo the process.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 20, 2011 at 6:07 am

    #1341107

    Thanks Everyone for the Response.

    I got it.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply