Post reply

Query to sum only few values in a same column

  • February 2, 2013 at 1:54 pm

    #268057

    Hello all,

    I am kind of new to sql server and I am stuck on this. It may sound stupid and simple but here is my situation:

    CREATE TABLE For_Test(ID int not null,Name varchar(255),Amount decimal(18,2))

    INSERT INTO For_Test VALUES (1,'A',220)

    ,(2,'B',340)

    ,(3,'C',540)

    ,(4,'D',780)

    I just want to add amount of ID 1,2 and 3 excluding 4. There will be more than four rows in a table. So, how to do this?

  • February 3, 2013 at 7:07 am

    #1583711

    Here is a quick method. NOT recommended for speed or flexibility ... If you would outline your requirements in greater detail perhaps some one can / will give you further assistance.

    CREATE TABLE For_Test(ID int not null,Name varchar(255),Amount decimal(18,2))

    INSERT INTO For_Test VALUES (1,'A',220),(2,'B',340),(3,'C',540)

    ,(4,'D',780)

    --I just want to add amount of ID 1,2 and 3 excluding 4.

    SELECT SUM(Amount) FROM For_Test WHERE ID in (1,2,3)

    --Different select statement but same result

    SELECT SUM(Amount) FROM For_Test WHERE ID <> 4

    --Yet a 3rd way

    SELECT SUM(Amount) FROM For_Test WHERE ID < 4

    --Yet a 4th way - using MOD function

    SELECT SUM(Amount) FROM For_Test WHERE ID % 4 <> 0;

    Result:

    (No column name)

    1100.00

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 3, 2013 at 8:11 am

    #1583717

    Thank you very much. This will work for me now.

  • February 3, 2013 at 11:27 am

    #1583733

    park1432 (2/3/2013)

    Thank you very much. This will work for me now.

    Since you're new, I have to ask, which one is the [font="Arial Black"]"This"[/font] you speak of and why did you choose it?

    --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)

  • February 4, 2013 at 2:52 pm

    #1584125

    What I would ask is "What characteristic/attribute of the fourth record makes you want to exclude it?"

  • February 4, 2013 at 6:05 pm

    #1584157

    @park1432,

    Two way street here. We're actually interested in your problem and would like a bit more info if, for nothing else, a little education on a strange request. 😉

    --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)

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

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