Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Posts
    58

    Exclamation Unanswered: Summing fields from 2 different tables into one

    This statement seems to be multiplying the Sums together instead of adding them. So if t2 sum is 72 and the t3 sum is 15 this statement will return 1080 for both. Am I doing the joins wrong? Any input is greatly appricated

    SELECT f1, f2, ISNULL(SUM(t2.f3),0), ISNULL(SUM(t3.f3), 0)
    FROM t1
    LEFT JOIN t2
    ON t1.prikey= t2.forkey
    LEFT JOIN t3
    ON t1.prikey= t3.forkey
    GROUP BY f1, f2
    HAVING NOT (ISNULL(SUM(t2.f3),0) + ISNULL(SUM(t3.f3), 0)) = 0
    ORDER BY f2

  2. #2
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Re: Summing fields from 2 different tables into one

    It's rather hard to answer this in the abstract SQL you've given. Not clear what you're trying to do. Which table are f2 and f3 in?

    If you could give me a full script with some 'create table' and 'insert into ... values ()' statements I might be able to figure out what you're doing.

    Don't forget, if f2 is in t2 and f3 is in t3, for every row in t3 that matches on the key with on f1 you'll get a copy of the joined row in t2.
    Basically, depending upon your data, the syntax can enable you to get cartesian products (that's probably why it multiplies rather than sums).

  3. #3
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Re: Summing fields from 2 different tables into one

    Try putting the group-by on the pk for t1.

Posting Permissions

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