Results 1 to 10 of 10

Thread: Sum Question

  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: Sum Question

    Hi,

    I have a table A having 2 columns one with Batch_Number and the other one with Amount. The Batch_Number column has a number which is not unique. ie

    Batch_Number Amount
    11 1234
    11 567
    11 3434343
    12 343434
    14 4544545
    15 2323
    15 4345555
    15 2323233

    In another table B I have field called Batch Number and Amount_Result
    What I want to achieve is to instert into the Amount_Result the sum of the Amount of table A of the same Batch_Number of table A into the Bacth number of table B.

    For instance

    Table A
    Batch_Number Amount
    11 1234
    11 567
    11 3434343

    Table B
    Batch Number Amount
    11 sum of amount in table A



    Can anyone please help me. I really need help on this.

    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to use the GROUP BY clause.

    SQL GROUP BY Statement

    Give it a go and post back your results
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    Thanks gvee. But I do not think group by is what I need.
    I want to insert into table B the result of the sum of amount for each of the same batch numbers into table b.

    Table B has a unique batch number so for instance

    Table A
    Batch_Number Amount
    11 1234
    11 567
    11 3434343

    The sum of the amount of all 11 shall be insterted into
    Table B
    Batch Number Amount
    11 sum of the amount of all 11 in table A

  4. #4
    Join Date
    Jan 2012
    Posts
    52
    mi mistake group by works, but how do i insert this into table b?

  5. #5
    Join Date
    Jan 2012
    Posts
    52
    group by gives me the sum of all the batch_numbers amount. So i am getting this now

    Table A
    Batch_Number Amount
    11 result

    but i need to isnert this into table. I am trying to make it like a join but is not working

    UPDATE TABLE B
    SET AMOUNT_RESULT =(SELECT A.BATCH_NUMBER, SUM (AMOUNT) GROUP BY BATCH_NUMBER)
    FROM TABLE A, TABLE B
    WHERE A.BATCH_NUMBER = B.BATCH_NUMBER

    PLEASE HELP

  6. #6
    Join Date
    Sep 2010
    Posts
    153
    UPDATE tableB SET AMOUNT = (SELECT SUM(AMOUNT) FROM tableA GROUP BY A.batch_number) where B.batch_number = (select max(A.batch_number) from tableA);


    P.S- I am in Office and don't have database installed in my system. I guess this query will work. Try it.


    Logic - If there are 3 same ids i.e 11, 11, 11 then max(ids) will give u one result and that is 11. Hence, we have one to one comparision now.

    All the best

  7. #7
    Join Date
    Sep 2010
    Posts
    153
    UPDATE tableB SET A.AMOUNT = (SELECT SUM(A.AMOUNT) FROM tableA A GROUP BY A.batch_number) where B.batch_number = (select max(A.batch_number) from tableA);

  8. #8
    Join Date
    Jan 2012
    Posts
    52
    I am still having troubles with this, please guys help me, this is what I got:

    Code:
      UPDATE EXP_JOURNALHEADER
            SET CTRL_DEBITS = (SELECT A.BATCH_NUMBER) FROM EXP_JOURNALHEADER A INNER JOIN
    (SELECT BATCH_NUMBER, SUM(DEBIT_AMOUNT) AS CTRL_DEBITS
            FROM EXP_JOURNALDATA GROUP BY BATCH_NUMBER)BATCH
            ON BATCH.BATCH_NUMBER = A.BATCH_NUMBER
    This do update the table but in the field CTRL_DEBITS rather than updating the sum it only puts the batch number

    Please help
    Last edited by buzmay; 03-06-12 at 09:44.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Not sure any more how the tables and columns are named, you may have to correct some. Try this:
    Code:
    UPDATE U
    SET U.AMOUNT_RESULT = T.SumAmount
    FROM EXP_JOURNALHEADER U
    	INNER JOIN (SELECT BATCH_NUMBER, 
    			SUM(AMOUNT) AS SumAmount
    			FROM EXP_JOURNALDATA 
    			GROUP BY BATCH_NUMBER
    			) AS T ON
    		U.BATCH_NUMBER = T.BATCH_NUMBER
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Jan 2012
    Posts
    52
    Million thanks guys for all your help. I really appreciate it, i have fixed this issue now thanks to all of you who replied Have a good day and to all you

Posting Permissions

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