SQL statement design help needed 1

[FinalPrime]

I'm new to SQL design beyond the simplistic of the simplistic

Column1 is an account-number from Table-A
Column2 is Value1 from Table-B
Column3 is Value2, a summed value from Table-C calculated by reading multiple rows based on the account number
Column4 is Value3, calculated by (Value1 / Value2)

Joining Table-A with Table-B returns one record per account as needed with the account-number and Value1.
Now putting in the Table3 join I get all the values I need to do the sum for Value2, but now have multiple rows.
It appears I need a sub-query to get Value2 and return as an alias.
Value3 then becomes Value1 / alias.
The Sub-query will need to have the aggregate function Sum
Am I on the right tract? Any help would be greatly qppreciated.

 

[nigelrivett]

select a.acno, b.value1, value2 = sum(c.value2),value3 = b.value1/sum(c.value2)
from tablea a
join tableb b
on b.acno = a.acno
join tablec c
on c.acno = a.acno
group by a.acno, b.value1

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[FinalPrime]

What if there were many more columns from Table-A, more columns B, etc.?
I'm coding linking 5 tables together and getting 12 columns.
Table 5 has the data in it I need to calculated a sum.

Would using GROUP BY clause on the a.acno require mutible GROUP BY clauses for
the other data elements?

(((( I had tried mutiple GROUP BY clauses and although it returned rows as needed, it couldn't
be used. Ran forever.

I tried to run with just one GROUP BY clause, but the machine gave error message 'data field
is invalid in the selection because it is not contained in either an aggregate function or
the GROUP BY clause. ))))

Your help is greatly appreciated

 

[hindsjl]

Some more info would be helpful now. Are the Table A columns coming from just one table, or more? Same question for the Table B columns. Could you list all five tables (or dumbed-down aliases for them) here, and give some idea of the joins between them?

John

 

[FinalPrime]

John,
I very much appreciate your help. I had over-simplified
the question. However, I have figured out an SQL that
runs quickly. It involves a sub-query. I had never used a
subquery. I'm new at this. Here is the code I have worked out. I now have learned it's almost as hard to ask the question as it is to write the code!
Again, I very much appreciate your help.

SELECT TblA.ParcelID, TblA.AccountNumber,
TblD.LUC, TblA.NBC,
TblC.YearBlt ,
(SELECT SUM(TblEas.FinishedArea)
FROM TblEas WHERE
TblEas.CardNumber = 1 AND
TblEas.AccountNumber =
TblA.AccountNumber) as sf ,
TblB.CurrentTotal, TblC.ExtType, TblB.CurrentTotal, TblB.CurrentSF
FROM ((TblA LEFT JOIN TblD ON (TblA.CardNumber = TblD.CardNumber) AND (TblA.AccountNumber = TblD.AccountNumber))
LEFT JOIN TblB ON (TblA.CardNumber = TblB.CardNumber)
AND (TblA.AccountNumber =
TblB.AccountNumber)) LEFT JOIN TblC ON
TblA.AccountNumber =
TblC.AccountNumber WHERE
(((TblA.AccountNumber)>0) AND
(CHARINDEX('R',TblC.ExtType) = 1 ) AND
((TblB.SeqNumber)=0) AND
((TblA.CardNumber)=1) AND
((TblD.CardNumber)=1) AND
((TblC.CardNumber)=1) AND
((TblB.CardNumber)=1) AND
((TblA.Closed)=0));

Final Prime

Rudyar Kipling at height of Fame:
"Appears my words are work a shilling each."
Oxford Student:
"Here's a shilling. Give me you best word"
Kipling:
"Thanks""