how to calculate row1 - row2 - row3....

  • I have a table which has some n no of rows. The table has Key, value1, value2, value3, etc fields. Now i need to insert a row which should be derived from the existing rows.

    eg: below are the data in the table

    KeyValue1Value2Value3

    ky01010015090

    ky02015020010

    ky03058040010

    ky04016010010

    ky05011025010

    ky06019028010

    ky07035027010

    I need to insert a row as below

    ky05531050-10

    the Value1, 2 and 3 are derived from the rows ky030 - ky040 - ky050

    If it addition i can simply write

    SELECT'ky055', SUM(Value1), SUM(Value2), SUM(Value3)

    FROM table_name

    WHERE Key in (ky030,ky040,ky050)

    but i need to subtract the rows...

    Can anyone help me to find a query to generate the value

  • Please read this.

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

    Here is a query that will make THIS specific example work.

    It rely's on your Key column to be ordered correctly.

    If the order of your keys is guaranteed then fine, but I would seriously look at the design of this table and data storage strategy before worrying about creating these queries.

    DECLARE @Table TABLE ( Varchar(10),Value1 INT, Value2 INT, Value3 INT)

    INSERT INTO @Table

    SELECT 'ky010', 100, 150, 90 UNION ALL

    SELECT 'ky020', 150, 200, 10 UNION ALL

    SELECT 'ky030', 580, 400, 10 UNION ALL

    SELECT 'ky040', 160, 100, 10 UNION ALL

    SELECT 'ky050', 110, 250, 10 UNION ALL

    SELECT 'ky060', 190, 280, 10 UNION ALL

    SELECT 'ky070', 350, 270, 10

    SELECT 'ky055',

    SUM(CASE WHEN ID =1 THEN Value1 ELSE Value1 * -1 END),

    SUM(CASE WHEN ID =1 THEN Value2 ELSE Value2 * -1 END),

    SUM(CASE WHEN ID =1 THEN Value3 ELSE Value3 * -1 END)

    FROM (

    SELECT RANK() OVER(ORDER BY [Key]) AS id, [Key], Value1, Value2, Value3

    FROM @table

    WHERE [Key] in ('ky030','ky040','ky050')

    )t

  • Thanks Ray,

    Its working...

    Also the article is nice once. I will follow that....

Viewing 3 posts - 1 through 2 (of 2 total)

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