sum of distint field 1 / sum of distinct field 2

[wshm]

I have a field 'actUnitCost' in form "frm_DeptBudget"(from tbl_DeptBudget).

This field needs to return

sum of 'tranXamt' from tbl_DeptActTxDetail /
sum of 'actProd' from tbl_DeptProGoalUpdate

I've learned today how to do group by and sum in query
today, thx to remou and phv, but I have no clue
how this can be done... i've tried avg and some other things
in sql but all i get is errors.

any help?

 

[AlexCuse]

I think this would do it for you:

select GroupingColumn 
,sum(tranXamt)/sum(actProd) as SomeColumnName
from tbl_DeptProGoalUpdate
group by GroupingColumn

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Wait I did not see that you had two tables involved. Try this :

select a.GroupingColumn
,sum(a.tranXamt)/sum(b.actProd) as SomeColumnName
from tbl_DeptActTxDetail a
inner join tbl_DeptProGoalUpdate b
on a.CommonColumn = b.CommonColumn
group by a.GroupingColumn

CommonColumn is something that is shared between the two tables, and GroupingColumn is whatever you want this calculation to be grouped by.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[wshm]

i'll give that a try. thx alex

 

[wshm]

i can't get it to work.. i worked it around in vba though..
a question tho.
if both columns are from the same table,
first statement should work right?
i've tried doing...

SELECT tbl_EstUnitCost.deptbudno
,sum(estcost)/sum(estprodunit) as tbl_estunitcost.estunitcost
FROM tbl_EstUnitCost
GROUP BY tbl_EstUnitCost.DeptBudNo;

but it says select statement is reserved.
how would i do this in update query?

maybe something like..

UPDATE tbl_EstUnitcost SET EstUnitcost
,sum(estcost)/sum(estprodunit) as estunitcost
FROM tbl_EstUnitCost
Group By tbl_EstUnitCost.DeptBudNo;

i know the syntax is wrong.. but i think you
can get the idea of what i am thinking..

 

[AlexCuse]

I think you are right, you can't use a table name in your column aleas. Did you try the second example (converted to a SELECT of course)

SELECT tbl_EstUnitCost.deptbudno
,sum(estcost)/sum(estprodunit) as estunitcost
FROM tbl_EstUnitCost
Group By tbl_EstUnitCost.DeptBudNo;

Ignorance of certain subjects is a great part of wisdom