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)
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
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).