Post reply

dividing a value by rowcount in fewest statements as possible?

  • February 8, 2010 at 8:31 am

    #96955

    Hi,

    I have the following basic data:

    rowid orderid cost

    1 1 10

    2 1 10

    3 1 10

    4 2 5

    5 2 5

    I would like to divide the the cost in each field by the count of the orderid and and replace the cost field value with this value the updated table would then have following data:

    rowid orderid cost

    1 1 0.3333

    2 1 0.3333

    3 1 0.3333

    4 2 2.5

    5 2 2.5

    I can do this using a #temp table but can this be done in one sql statement?

    An elegant solution would be great to have and I could learn from it! 🙂

    Thanks

    Chris

  • February 8, 2010 at 8:46 am

    #1115558

    Any particular version of SQL Server?

  • February 8, 2010 at 8:53 am

    #1115567

    -- SQL Server 2005/2008 only

    SELECT rowid,

    orderid,

    CAST(cost AS DECIMAL(10,4)) / CAST(COUNT(*) OVER(PARTITION BY orderid) AS DECIMAL(10,4)) AS cost

    FROM mytable

    ORDER BY rowid

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 8, 2010 at 8:53 am

    #1115568

    Well, It is on SQL Server 2000 but eventually it will be moved to SQL Server 2005.

    Thanks

  • February 13, 2010 at 7:01 am

    #1118427

    I am using SQL server 2000. So can I use that on this version or I have to move to 2005 or later...

  • February 13, 2010 at 6:56 pm

    #1118509

    haggisns (2/8/2010)

    Hi,

    I have the following basic data:

    rowid orderid cost

    1 1 10

    2 1 10

    3 1 10

    4 2 5

    5 2 5

    I would like to divide the the cost in each field by the count of the orderid and and replace the cost field value with this value the updated table would then have following data:

    rowid orderid cost

    1 1 0.3333

    2 1 0.3333

    3 1 0.3333

    4 2 2.5

    5 2 2.5

    I can do this using a #temp table but can this be done in one sql statement?

    An elegant solution would be great to have and I could learn from it! 🙂

    Thanks

    Chris

    Chris,

    Since you're new, a bit of advice... if you want an "elegant" solution, then make it easy on the people that are trying to help you. Always post your data in a "readily consumable" format so people can test their code before posting it. You'll be amazed at how quickly you get good answers if you do. Please see the article at the first link in my signature line below.

    Here's how to solve the problem in SQL Server 2000...

    --===== Create a test table and populate it.

    -- This is not a part of the solution.

    -- This is the way you SHOULD post data for your questions.

    CREATE TABLE #TestTable

    (rowid INT, orderid INT, cost DECIMAL(9,2))

    INSERT INTO #TestTable

    (rowid, orderid, cost)

    SELECT 1,1,10 UNION ALL

    SELECT 2,1,10 UNION ALL

    SELECT 3,1,10 UNION ALL

    SELECT 4,2,5 UNION ALL

    SELECT 5,2,5

    --===== Tested solution to your problem

    SELECT tt.rowid, tt.orderid, tt.cost/d.orderid_rowcount

    FROM #TestTable tt

    INNER JOIN

    ( --=== Find the rowcount for each orderid

    SELECT orderid, COUNT(*) AS orderid_rowcount

    FROM #TestTable

    GROUP BY orderid

    )d

    ON tt.orderid = d.orderid

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

  • July 3, 2010 at 5:50 pm

    #1188586

    haggisns (2/8/2010)

    An elegant solution would be great to have and I could learn from it! 🙂

    Thanks

    Chris

    So! Did you learn anything from 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)

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

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