Results 1 to 2 of 2

Thread: Help with SUM

  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Help with SUM

    My data in table looks like this -
    Col1 Col2
    char (3) int
    ---- --------
    100 50
    110 60
    200 100
    300 200
    350 300

    I am being told that the values 100 and 110 in Col1 have been entered incorrectly. They should have been entered as 101 and Col2 data for them needs to be summed up. So I need -

    Col1 Col2
    char (3) int
    ---- --------
    101 110 (50+60)
    200 100
    300 200
    350 300

    in the resultant table. How do I accomplish this with a query.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You should make sure you have a primary key

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 char(3) PRIMARY KEY, Col2 int)
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT '100', 50  UNION ALL
    SELECT '110', 60  UNION ALL
    SELECT '200', 100 UNION ALL
    SELECT '300', 200 UNION ALL
    SELECT '350', 300
    GO
    
    SELECT * FROM myTable99
    
    INSERT INTO myTable99(Col1,Col2)
    SELECT '101', SUM(Col2) FROM myTable99 
     WHERE Col1 IN ('100','110') AND Col2 IN (50,60)
    
    DELETE FROM myTable99
     WHERE Col1 IN ('100','110')
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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