Re: SQL Join for a Calculation - Mailing list pgsql-general

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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)