SQL Join for a Calculation

From: "Kojak" <kojak7(at)rogers(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: SQL Join for a Calculation
Date: 2006-11-12 00:25:51
Message-ID: 1163291151.001348.53970@i42g2000cwa.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Here's a description of the scenario. The question I'm asking follows
the description.
3 tables
table1:
job_no int4
rate1 float4
qty1 float4
rate2 float4
qty2 float4
rate3 float4
qty3 float4

table2:
job_no int4
part_id int4
rate float4
qty float4

table3:
job_no int4
desc varchar(32)
rate float4
qty float4

These are the condensed table structures. Table 1 will only have 1
record per job. This is the 1 of the 1-to-many relationship to table2
and table3. Table2 has 2 records in it, and table3 has 3 records in
it. What I want to retrieve is the sum of all of the rate*qty.
Therefore, in a single field in the SQL statement, I want
((rate1*qty1)+(rate2*qty2)+(rate3*qty3))+(sum of table2
(rate*qty))+(sum of table3 (rate*qty)). Then I have to apply some
discounts and taxes to this, but that is the next phase....

For example:
table1:
job_no 500
rate1 1500
qty1 1
rate2 500
qty2 1
rate3 75
qty3 10

table2: (2 records)
job_no 500
part_id 3, 10
rate 500, 2000
qty 2, 1

table3: (3 records)
job_no 500
desc 'text1','text2','text3'
rate 100, 150, 200
qty 5, 5, 5

The cost for the example should work out to be:
table1 (2750) + table2 (3000) + table3 (2250) = 8000
Thoughts?

Say we forget about the rates and qtys in table 1 for now. If we just
try and calculate the total for the rates and qtys in table2 and table3
for job_no 500...? break it down even further and return 2 columns, 1
column for the total of table1 and 1 column for the total of table2...?
In my query, where I do:
select
table1.job_no,sum(table2.rate*table2.qty),sum(table3.rate*table3.qty)
from table1,table2,table3 where table1.job_no=500 and
table1.job_no=table2.job_no and table1.job_no=table3.job_no group by
job_no

the result I get is
500, 9000, 2250

the sum of table2 is being multiplied by the number of records in
table3. I understand why, but don't know how to resolve it....

If I do:
select
((rate1*qty1)+(rate2+qty2)+(rate3*qty3)),(table2.rate*table2.qty),(table3.rate*table3.qty)
from table1,table2,table3 where table1.job_no=500 and
table1.job_no=table2.job_no and table1.job_no=table3.job_no

the result is
2750, 1000, 500
2750, 1000, 750
2750, 1000, 1000
2750, 2000, 500
2750, 2000, 750
2750, 2000, 1000

Therefore, if I include a SUM() on these fields, you see where the
problem lies....the result would be:
16500, 9000, 4500

what I want is 2750, 3000, 2250
Thoughts?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-11-12 00:43:40 Re: Speed of postgres compared to ms sql, is this article/comment off?
Previous Message Martijn van Oosterhout 2006-11-11 22:19:03 Re: encoding advice requested