join operation syntax needed

[xcata]

Hi,
I have a strange problem with calculating some amounts

Ex.
create table Contracts(contract_id int,contract_description varchar(15))
create table Quittances(quitt_contract_id int, quitt_amount decimal(12,2))
create table Others(oth_contract_id int, oth_amount decimal(12,2))

insert into contracts values (1,'Con1')

insert into quittances values(1,100)
insert into quittances values(1,200)
insert into quittances values(1,300)

insert into Others values(1,45)
insert into Others values(1,35)

What I need is to obtain the sum of quittances and the sum of others but in the same query.
Of course it works with a subquery or with two separate joins but I need like that because of an application.

the following query does not work because of inner join who multiply the rows.
select
contract_description,
sum(quitt_amount) as SM,
sum(oth_amount) as OM
from Contracts
inner join Quittances on contract_id = quitt_contract_id
inner join Others on contract_id = oth_contract_id
group by contract_description

The results I expect are
con1 600 70
Had anyone know a sollution?

Thanks.

 

[bperry]

Would an approach using defined tables be suitable to what you are doing?
---------------------
Select contract_description, quitt_amount, oth_amount
from CONTRACTS

INNER JOIN

(Select quitt_contract_id, sum(quitt_amount) as quitt_amount
from quittances group by quitt_contract_id) as qdt

ON Contracts.contract_id = qdt.quitt_contract_id

INNER JOIN

(Select oth_contract_id, sum(oth_amount) as oth_amount
from Others group by oth_contract_id) as odt

ON Contracts.contract_id = odt.oth_contract_id
------------------------

 

[bperry]

By the way, is it always the case that records that exist in Contracts will also always exist as well in Quittances and Others. If so, then the INNER JOIN will probably be okay.

But if you need to report records that are in Contracts but NOT in either Quittances or Others, then changing the Inner Joins to Left Joins should resolve that.

brian perry

 

[xcata]

No, the derived table are not good because I need this query for an application who knows only simple queries.

For the second answer, what I need is the sum of quitt and the sum of others for each contract.

In my opinion does not exist a sollution but maybe I'm wrong.

Thnaks anyway.

 

[bperry]

I agree with you.
No, this cannot be done without a subquery, defined tables, or separate joins.

bp

 

[Andel]

Will this work?


select c.contract_description,
sum(q.quitt_amount) / count(c.contract_id) * count(distinct q.quitt_amount) as SM,
sum(o.oth_amount) / count(c.contract_id) * count(distinct o.oth_amount) as OM
from Contracts c
inner join Quittances q on c.contract_id = q.quitt_contract_id
inner join Others o on q.quitt_contract_id = o.oth_contract_id
group by c.contract_description

Andel
andel@barroga.net

http://andyb.8m.com