Skip site navigation (1) Skip section navigation (2)

Re: Querying multiple tables

From: Paul Malherbe <paul(at)tartan(dot)co(dot)za>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Querying multiple tables
Date: 2007-08-01 19:04:31
Message-ID: 46B0D93F.7020909@tartan.co.za (view raw or flat)
Thread:
Lists: pgsql-novice
Michael Glaesemann wrote:
>
> On Aug 1, 2007, at 11:36 , Paul Malherbe wrote:
>
>> I tried your suggestion and ended up with the same problem I was 
>> experiencing i.e. because there are multiple owner and tenant 
>> transaction records the sum of the values are multiplying by the 
>> number of transactions i.e.
>
>> Help!
>
> Well, please help us help you! Could you please provide the table 
> definitions and the query?
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
Hi

Sorry about that, here is an example of what I am doing and what I would 
like to achieve.

Create table rcaowm (rom_owner varchar(7), rom_name varchar(30));
Create unique index rcaowm_key1 on rcaowm (rom_owner);
Insert into rcaowm values ('OOOOOOO', 'Owners Name');

Create table rcaowt (rot_owner varchar(7), rot_date int4, rot_tramt 
decimal);
Create index rcaowt_key1 on rcaowt (rot_owner);
Insert into rcaowt values ('OOOOOOO', 20070801, -3500.0);
Insert into rcaowt values ('OOOOOOO', 20070801, -500.0);
Insert into rcaowt values ('OOOOOOO', 20070801, 399.0);

Create table rcatnm (rtm_owner varchar(7), rtm_tenant varchar(7), 
rtm_name varchar(30));
Create unique index rcatnm_key1 on rcatnm (rtm_owner, rtm_tenant);
Insert into rcatnm values ('OOOOOOO', 'TTTTTTT', 'Tenants Name');

Create table rcatnt (rtt_owner varchar(7), rtt_tenant varchar(7), 
rtt_date int4, rtt_tramt decimal);
Create index rcatnt_key1 on rcatnt (rtt_owner, rtt_tenant);
Insert into rcatnt values ('OOOOOOO', 'TTTTTTT', 20070801, 3500.0);
Insert into rcatnt values ('OOOOOOO', 'TTTTTTT', 20070801, 500.0);

Select rom_name as "Owner_Name", rtm_name as "Tenant_Name", 
sum(rot_tramt) as "Sum_Owner_Value" , sum(rtt_tramt) as 
"Sum_Tenant_Value" from rcaowm join rcaowt on (rom_owner = rot_owner) 
join rcatnt on (rom_owner = rtt_owner) join (select rtm_tenant, rtm_name 
from rcatnm) as tenants on (rtm_tenant = rtt_tenant) group by rom_name, 
rtm_name;

This query returns:

 Owner_Name  | Tenant_Name  | Sum_Owner_Value | Sum_Tenant_Value
-------------+--------------+-----------------+------------------
 Owners Name | Tenants Name |         -7202.0 |          12000.0


I would like to return:

 Owner_Name  | Tenant_Name  | Sum_Owner_Value | Sum_Tenant_Value
-------------+--------------+-----------------+------------------
 Owners Name | Tenants Name |         -3601.0 |           4000.0


Regards, Paul

In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-08-01 20:49:31
Subject: Re: Querying multiple tables
Previous:From: Michael GlaesemannDate: 2007-08-01 17:58:13
Subject: Re: Querying multiple tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group