Re: SQL Join for a Calculation

You want to do count(DISTINCT part_id) and count(DISTINCT desc).

On Sat, Nov 11, 2006 at 04:25:51PM -0800, Kojak wrote:
> 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?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>

--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)