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