Michael Weissenbacher wrote:

> I know that it was just an example. But your assumption that you need to
> know the number of records in advance is wrong. AFAIK the divisor
> depends on the number of tables joined.
> for 2: 1
> for 3: 3
> for 4: 6
> for 5: 10
> for 6: 15
> for n: (n-1)*n/2

Nope, it depends on the number of matching records in the other
table(s). With his example:

INSERT INTO MAINTABLE(ID) VALUES(1);
INSERT INTO SALESTABLE(ID, SALES) VALUES(1, 10.00);
INSERT INTO SALESTABLE(ID, SALES) VALUES(1, 15.00);
INSERT INTO COSTSTABLE(ID, COSTS) VALUES(1, 5.00);
INSERT INTO COSTSTABLE(ID, COSTS) VALUES(1, 8.00);
INSERT INTO COSTSTABLE(ID, COSTS) VALUES(1, 1.00);

doing

select m.id, s.sales, c.costs
from maintable m
left join salestable s on s.id = m.id
left join coststable c on c.id = m.id
where m.id = 1

would result in

1, 10, 5
1, 10, 8
1, 10, 1
1, 15, 5
1, 15, 8
1, 15, 1

To get the sum of the individual table, he would want sum(s.sales)/3 and
sum(c.costs)/2.

In a formula, what he want is something like:

select m.id, sum(s.sales)/(select count(*) from coststable c2
where c2.id = m.id), sum(c.costs)/(select count(*) from salestable s2
where s2.id = m.id)
from maintable m
left join salestable s on s.id = m.id
left join coststable c on c.id = m.id
where m.id = 1
group by m.id

Though I cannot think of any situation where this would be preferrable
to a simple subselect.

Set