Post reply

sum on specific column

  • February 16, 2012 at 4:07 am

    #264521

    hi ,

    maybe some one can help me:

    i was ask to summarize column on sql table :

    1

    2

    3

    7

    8

    sum=21

    without using sum() function ,without loop, without cursor ,without using any other temp table or column.

    any idea ?

    thanks a lot sharon.

  • February 16, 2012 at 4:31 am

    This was removed by the editor as SPAM

  • February 16, 2012 at 5:06 am

    #1447780

    thank you vary much ,

    its was what i looking for .

    what its starnge for me is how sql now to take each value from acloumn 🙂

    this command :

    select @sum =@sum + col1 from #test

    return last value from table

    and this command :

    select @sum =@sum + col1 from #test

    now how to go over all the column if there is goog

    explain i love to hear 🙂

    thank yuo anyway

    sharon

  • February 16, 2012 at 5:27 am

    This was removed by the editor as SPAM

  • February 16, 2012 at 5:31 am

    #1447786

    thank you vary much

    you help me alot

    sharon

  • February 16, 2012 at 7:05 am

    #1447852

    Declare @sum int

    Select @sum=coalesce(@sum+'+','') + item_id from inventory.inventory_transaction

    select @sum

    I guess this will help u..

    Regard

    Guru

  • February 16, 2012 at 7:45 am

    #1447886

    sharon-472085 (2/16/2012)

    hi ,

    maybe some one can help me:

    i was ask to summarize column on sql table :

    1

    2

    3

    7

    8

    sum=21

    without using sum() function ,without loop, without cursor ,without using any other temp table or column.

    any idea ?

    thanks a lot sharon.

    Based on the criteria for that problem, was this an interview question? The reason why I ask is that such a question might be asked to see if you understand the looping that SQL Server does behind the scenes (some of us call them "Pseudo Cursors").

    --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 16, 2012 at 10:54 pm

    #1448266

    Stewart "Arturius" Campbell (2/16/2012)

    will this help?

    This is an unreliable and unsupported construct, and should not be used or recommended:

    CREATE TABLE #test (col1 tinyint, col2 char(5));

    INSERT #test (col1)

    VALUES (1),(2),(3),(7),(8);

    DECLARE @sum tinyint = 0;

    SELECT @sum += col1 FROM #test ORDER BY col1 + 0

    SELECT @sum

    Result: unpredictable, but probably 8 (which is wrong).

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 16, 2012 at 10:59 pm

    #1448268

    sharon-472085 (2/16/2012)

    without using sum() function ,without loop, without cursor ,without using any other temp table or column.

    A bit of a cheat, but:

    SELECT

    result =

    CONVERT(tinyint, AVG(CONVERT(decimal(9,4), t.col1)) * COUNT_BIG(*))

    FROM #test AS t;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 19, 2012 at 7:46 am

    #1448987

    hi,

    yes it was interview question

    (bad question for me:)

    thank sharon

  • February 19, 2012 at 10:50 am

    #1448995

    SQL Kiwi (2/16/2012)

    Stewart "Arturius" Campbell (2/16/2012)

    will this help?

    This is an unreliable and unsupported construct, and should not be used or recommended:

    CREATE TABLE #test (col1 tinyint, col2 char(5));

    INSERT #test (col1)

    VALUES (1),(2),(3),(7),(8);

    DECLARE @sum tinyint = 0;

    SELECT @sum += col1 FROM #test ORDER BY col1 + 0

    SELECT @sum

    Result: unpredictable, but probably 8 (which is wrong).

    First, your example is quite a bit different than Stewart's. And, there is no need for an ORDER BY IMHO. Second, do you have an example to a link which demonstrates the "unreliability" of the method or perhaps a link to such a demonstration?

    I'm not asking as a challenge. I'm asking because I want to know.

    --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 19, 2012 at 9:50 pm

    #1449061

    Jeff Moden (2/19/2012)

    First, your example is quite a bit different than Stewart's. And, there is no need for an ORDER BY IMHO. Second, do you have an example to a link which demonstrates the "unreliability" of the method or perhaps a link to such a demonstration?

    I'm not asking as a challenge. I'm asking because I want to know.

    Sort of: http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx. As you can see, the 'variable concatenation' trick was only supported for backward compatibility with the (unintentional) behaviour of older versions. That link is dated July 2005. Experience with more modern releases of SQL Server shows that even if backward compatibility is still being maintained for the most basic form, it has not been updated to take into account features added in the last 7 years (including the ability to ORDER BY an expression rather than a column in the SELECT list). The point of my demo was to highlight that fact, because people tend to assume this technique will work generally.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 12 posts - 1 through 11 (of 11 total)

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