Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Unanswered: Sum values in one column

    Hello

    I have the following situation: in table I have the columns: 'IdSum', 'IdAge', 'Age''.It looks:

    Code:
    IdSum   IdAge   Age
    1          1      20 
    2          2      22     
    3          3      40
    4          NULL  NULL
    5          4      50 
    6          5      67
    7          6      30
    8          7      45
    9          NULL  NULL
    10         8      10
    11         9      37
    12         10    20 
    13         11    45
    14         NULL  NULL
    Script:
    Code:
    create table AgeSum (
    IdSum int  identity,
    IdAge  int  null,
    Age    int  null)
    
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(1,1,20);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(2,2,22);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(3,3,40);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(4,NULL,NULL);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(5,4,50);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(6,5,67);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(7,6,30);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(8,7,45);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(9,NULL,NULL);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(10,8,10);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(11,9,37);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(12,10,20);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(13,11,45);
    INSERT INTO dbo.AgeSum (IdSum,IdAge,Age) VALUES(14,NULL,NULL)
    Id'like to sum up all values from column Age above NULL in this column.
    So, it shoud look:

    Code:
    IdSum   IdAge   Age
    1          1      20 
    2          2      22     
    3          3      40
    4          NULL  82 (20+22+40) - 3 columns above
    5          4      50 
    6          5      67
    7          6      30
    8          7      45
    9          NULL  192 (50+67+30+45) -  4 columns above
    10         8      10
    11         9      37
    12         10    20 
    13         11    45
    14         NULL  112 (10+37+20+45) -  4 columns above
    I try this code, but it not work correctly

    Code:
    update AgeSum
    set Age = sum (A.Age)
    from AgeSum A
    where A.IdAge is null
    I have no idea, how I should solve this problem.
    Thanks awfully for help.
    Regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by L@uk View Post
    Id'like to sum up all values from column Age above NULL in this column.
    please define "above"

    as you know, rows in a relational table have no sequence

    any sequence yo need must be provided by the values of some column

    should IdSum be used for this purpose?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    8
    Thanks for reply. I'd solved this problem and this is my code. I hope it will be reply for your question:

    Code:
    update AgeSum
    set Age = (select sum(Age) from AgeSum where IdAge between 1 and 3)
    where IdSum = 4
    
    
    update AgeSum
    set Age = (select sum(Age) from AgeSum where IdAge between 4 and 7)
    where IdSum = 9
    
    
    update AgeSum
    set Age = (select sum(Age) from AgeSum where IdAge between 8 and 11)
    where IdSum = 14
    It works, but I have a lot of columns to sum, so can I join above statemets in one statement?
    I try, but it doesn't work.

    Code:
    update AgeSum
    set Age = (select sum(Age) from AgeSum)
    where IdSum = case when IdAge between 1 and 3 then 4 
                  when IdAge between 4 and 7 then 9 
                  when IdAge between 8 and 11 then 14
                  end
    Thanks awfully for help.
    Regards

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE AgeSum
       SET Age = these_rows.sum_age 
      FROM (
           SELECT which_rows.this_row
                , SUM(t.Age) AS sum_age
             FROM (
                  SELECT this.IdSum AS this_row	
                       , prev.IdSum AS prev_row
                    FROM AgeSum AS this
                  LEFT OUTER 
                    JOIN AgeSum AS prev
                      ON prev.IdSum = ( SELECT MAX(IdSum)
                                          FROM AgeSum
                                         WHERE IdSum < this.IdSum 
                                           AND IdAge IS NULL )
                   WHERE this.IdAge IS NULL
                   ) AS which_rows
           INNER
             JOIN AgeSum AS t
               ON t.IdSum < which_rows.this_row
              AND ( 
                  t.IdSum > which_rows.prev_row
               OR which_rows.prev_row IS NULL
                  )
           GROUP
               BY which_rows.this_row
           ) AS these_rows
    INNER
      JOIN AgeSum AS t2
        ON t2.idsum = these_rows.this_row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2009
    Posts
    8
    Thank you very much. I hope, solution for this problem will be less complicated

    Regards

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by L@uk View Post
    I hope, solution for this problem will be less complicated
    why do you hope that?

    did you test my query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2009
    Posts
    8
    Yes, I did.
    I mean, this problem isn't very very difficult (each statement separately), but to join each - should write much more code.

    Regards

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by L@uk View Post
    ... but to join each - should write much more code.
    you want ~much more~ code than what i wrote?

    sorry, i don't think so

    and i don't think you can get the job done with less code, although you're welcome to try
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One NZDF solution might be:
    Code:
    SELECT this.IdSum, this.IdAge, Coalesce(this.Age
    , (SELECT Sum(y.Age) FROM dbo.AgeSum AS y
    WHERE  y.IdSum BETWEEN (SELECT IsNull(Max(z.IdSum)
    , 1) FROM dbo.AgeSum AS z WHERE  z.IdSum < this.IdSum
    AND z.Age IS NULL) AND this.IdSum)) FROM dbo.AgeSum AS this
    ORDER BY this.IdSum
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the operative word being "might"

    it sure looked to me like the original problem was to update the table, not just select from it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good point, I'm just allergic to the idea of ruining my test case. Since a SELECT operation leaves the data intact, it is my tool of preferene. The same logic could be made destructive if that's what is required, but it wouldn't be my first choice.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, i understand your rationalization

    still, you should give the UPDATE a try...

    also, your deviosity factor is pretty close to zero, my friend -- i had no trouble understanding your solution (although for some reason was unable to come up with it myself)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2010
    Posts
    3

    Smile please chek for this if it is useful

    Declare @idSumNext as integer
    Declare @idSumPrev as integer
    Declare @I as integer
    set @idSumNext = (select top 1 IdSum from AgeSum where IdAge is null)
    set @idSumPrev = 1
    set @I= (select count(*) from AgeSum where IdAge is null)


    while(@I>0)
    begin
    update AgeSum
    set Age = (select sum(Age) from AgeSum where IdAge between @idSumPrev and @idSumNext)
    where IdAge is null and IdSum = @idSumNext
    Set @idSumPrev = @idSumNext
    Set @idSumNext = (select IdSum from AgeSum where IdAge is null and IdSum>@idSumPrev)
    Set @I=@I-1
    End

  14. #14
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Maybe someone could help me to understand why the table has a summary value as a row of data?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by corncrowe View Post
    Maybe someone could help me to understand why the table has a summary value as a row of data?
    I think a few of use would like to know that....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •