Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: join two tables and sum same-named variable

    I'm joining two tables and need to sum 72 variables that appear in both tables.

    Here is an example, as well as the code I've tried (using 3 of the 72 variables).
    Attached Thumbnails Attached Thumbnails sample.JPG   sample2.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it looks like you're running SAS, not SQL Server

    perhaps you could start a new thread in Applications & Tools - dBforums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    It's still SQL code help that I'm looking for. I know I'm running SAS, but Proc Sql uses industry standard SQL code.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Tracee View Post
    ... Proc Sql uses industry standard SQL code.
    in that case, i think you'll need to change the GROUP BY clause to use column names, rather than ordinal positions

    also, you need to fix the "ambiguous column" errors
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You can use ordinal numbers for ORDER BY, not for GROUP BY.

    Based on the required output, you must change the implicit inner join (... FROM A, B WHERE A.id = B.id ...) with a FULL OUTER JOIN (... FROM A FULL OUTER JOIN B ON A.id = B.id ...)

    replace
    SUM(all_1)
    by
    SUM(COALESCE(d.all_1, 0) + COALESCE(f.all_1, 0)) as all_1
    Do the same for all_2 and all_3 (and the rest of the 72 variables).
    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

Posting Permissions

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