Hello,

I have three separate paradox tables: department, incom and expens.
How can I join sums from two tables, and group them by department
number?

Separate queries works fine:

SELECT dept.id, dept.name, SUM(incom.suma)
FROM dept
LEFT OUTER JOIN incom ON dept.id=incom.dept_id
GROUP BY dept.id, dept.name

and

SELECT dept.id, dept.name, SUM(expens.suma)
FROM dept
LEFT OUTER JOIN expens ON dept.id=expens.dept_id
GROUP BY dept.id, dept.name

but when I tried to join these queries into one, all sums was
multiplied by rows number:

SELECT dept.id, dept.name, SUM(incom.suma), SUM(expens.suma)
FROM dept
LEFT OUTER JOIN incom ON dept.id=incom.sp_id
LEFT OUTER JOIN expens ON dept.id=expens.sp_id
GROUP BY dept.id, dept.name

same result with WHERE clause.
====================
AB "Anyksciu vynas"
Software Engineer
====================

You need a keyfield (or a combination of fields that make up the key)
on the 'incom' and 'expens' table to get it done:

  SELECT dept.id, dept.name,
     SUM(incom.suma)/COUNT(distinct expens.keyfield),
     SUM(expens.suma)/COUNT(distinct incom.keyfield)
  FROM dept
  LEFT OUTER JOIN incom ON dept.id=incom.sp_id
  LEFT OUTER JOIN expens ON dept.id=expens.sp_id
  GROUP BY dept.id, dept.name

A more general form, that allows for more than 2 tables being joined,
is this:
  SELECT dept.id, dept.name,
     SUM(incom.suma)*COUNT(distinct incom.keyfield)/COUNT(dept.id),
     SUM(expens.suma)*COUNT(distinct expens.keyfield)/COUNT(dept.id)
  FROM dept
  LEFT OUTER JOIN incom ON dept.id=incom.sp_id
  LEFT OUTER JOIN expens ON dept.id=expens.sp_id
  GROUP BY dept.id, dept.name

If you don't have one keyfield for the tables, but for example two
string fields that make up the key, you can replace
    incom.keyfield
with
    incom.strfield1 || incom.strfield2

Hope this helps,

Jan