SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question subtracting two diff columns

    Hi

    I am confused how to do this.

    I have two tables and each table has an "Amount" colum.


    I need to sum up all the rows for column "Amount" in table A and sum up all the rows for column "Amount" in table B

    and then subtract those two colums.

    Here is what I am doing:


    Code:
    select tableA.city, sum(tableA.amount)  - sum(tableB.amount)
    from tableB,tableA
    where 
    tableB.city='Hyderabad' and 
    tableB.city=tableA.city
    group by tableB.city

    Say: tableA Has the following data for column "Amount" and city=Hyderabad:

    Code:
    111
    5
    10

    AND tableB Has the following data for column "Amount" and city=Hyderabad:


    Code:
    6
    5

    Now, my above query should do the following:

    Sum all amounts for tableA, which comes to 126
    Sum all amounts for tableB, which comes to 11


    So 126 - 11 = 115

    But I get the result 219

    Any idea wots going on and how to fix it?


    Thanx

  2. #2
    SitePoint Zealot smadeira's Avatar
    Join Date
    Oct 2003
    Location
    Pennsylvania
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are getting Table A summed more than once because of your join. get rid of the subtraction of the sum()s and just print sum(a) and sum(b) and run the query to see how the joins are causing things to be double counted.

    Code MySQL:
    select tableA.city, 
        sum(tableA.amount), 
        sum(tableB.amount)
    from 
        tableB,tableA
    where 
        tableB.city='Hyderabad' and 
        tableB.city=tableA.city
    group by 
        tableB.city
    Scott

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Joining these two tables on city, will give the following result:

    Code:
    111  6
    111  5
      5  6
      5  5
     10  6
     10  5
    This gives 252 - 33 = 219

  4. #4
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Joining these two tables on city, will give the following result:

    Code:
    111  6
    111  5
      5  6
      5  5
     10  6
     10  5
    This gives 252 - 33 = 219
    Wots the solution then?

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Quote Originally Posted by smadeira View Post
    You are getting Table A summed more than once because of your join. get rid of the subtraction of the sum()s and just print sum(a) and sum(b) and run the query to see how the joins are causing things to be double counted.

    Code MySQL:
    select tableA.city, 
        sum(tableA.amount), 
        sum(tableB.amount)
    from 
        tableB,tableA
    where 
        tableB.city='Hyderabad' and 
        tableB.city=tableA.city
    group by 
        tableB.city
    What I am doing is incorrect and there is a way?

    OR

    Is it something not possible in one query?

    Thanx

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    Wots the solution then?
    obviously, the solution is: don't join



    Quote Originally Posted by cancer10 View Post
    I need to sum up all the rows for column "Amount" in table A and sum up all the rows for column "Amount" in table B

    and then subtract those two colums.
    Code:
    SELECT ( SELECT SUM(Amount) FROM tableA )
         - ( SELECT SUM(Amount) FROM tableB ) AS difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    obviously, the solution is: don't join





    Code:
    SELECT ( SELECT SUM(Amount) FROM tableA )
         - ( SELECT SUM(Amount) FROM tableB ) AS difference
    That worked, but can it also show the cities also incase there are more then one city?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, that solution works for the question you posted

    if you change the question, then the solution has to change too -- obviously

    please explain what you mean by "more than one city"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm sorry, that solution works for the question you posted

    if you change the question, then the solution has to change too -- obviously

    please explain what you mean by "more than one city"

    Sorry about that.

    See, I might have multiple cities in both tableA and tableB.

    I want to do a sum of all rows for column "Amount" for each city in tableA and then do a sum of all rows for column "Amount" for each city in tableA
    then subtract the amount from both tables city wise.





    Thanx



    EDIT: So that it looks like


    City | Difference
    ---------------------
    City A | 55
    City B | 22
    City C | 543
    City D | 212

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    I want to do a sum of all rows for column "Amount" for each city in tableA and then do a sum of all rows for column "Amount" for each city in tableA
    then subtract the amount from both tables city wise.
    Code:
    SELECT a.city
         , a.total
         , b.total
         , a.total - b.total AS difference
      FROM ( SELECT city
                  , SUM(amount) AS total
               FROM tableA
             GROUP
                 BY city ) AS a
    LEFT OUTER
      JOIN ( SELECT city
                  , SUM(amount) AS total
               FROM tableB
             GROUP
                 BY city ) AS b
        ON b.city = a.city
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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