Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Unanswered: Combined Sum of Query From Two Tables?

    I'm running a query to show the transfer fees spent by a club with data from two tables :

    Code:
    SELECT SUM([Transfer Fee]) From [Summer_2001_2011] WHERE [New Club] LIKE 'Manchester City'
    Union All
    SELECT SUM([Transfer Fee]) From [Winter_2001_2011] WHERE [New Club] LIKE 'Manchester City'
    Shows the sum from each table :

    Code:
    545.01
    110.98
    What do I need to add to the query to show the total from both tables?

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Possibly a join with like condition? One select sum(Transfer Fee)....or do the union into tmp table and then sum total on Transfer Fee?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT SUM(subtotal) AS total
      FROM ( SELECT SUM([Transfer Fee]) AS subtotal
               FROM [Summer_2001_2011] 
              WHERE [New Club] = 'Manchester City'
             UNION All
             SELECT SUM([Transfer Fee]) 
               FROM [Winter_2001_2011] 
              WHERE [New Club] = 'Manchester City' ) AS u
    oh, and has anyone said this yet...
    Btw: this is a very good example on why such a table structure is a bad idea. You should have used a single table right from the start.
    jk, i know they have
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by r937 View Post
    oh, and has anyone said this yet...I believe so....
    Btw: this is a very good example on why such a table structure is a bad idea. You should have used a single table right from the start.
    jk, i know they have
    This is true. So very true.

  5. #5
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by r937 View Post
    Code:
    SELECT SUM(subtotal) AS total
      FROM ( SELECT SUM([Transfer Fee]) AS subtotal
               FROM [Summer_2001_2011] 
              WHERE [New Club] = 'Manchester City'
             UNION All
             SELECT SUM([Transfer Fee]) 
               FROM [Winter_2001_2011] 
              WHERE [New Club] = 'Manchester City' ) AS u
    oh, and has anyone said this yet...
    Btw: this is a very good example on why such a table structure is a bad idea. You should have used a single table right from the start.
    jk, i know they have
    Thanks and yes they have said it.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    One of my probleams when they bring me in after everyone else has failed

    "Who Freakin (or other expletives) built this pile of horse stuff (again)"

    They don't like it, but I fix it
    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
  •