Background:

We are doing a price adjustment in our database. The price adjustment is based on the bill of materials, and what each materials' new cost is. So I have to select the item from the item table, its BOM, and the new price table, and adjust the price based on the total of the materials it takes to make each item. Items are comprised of varying quantities of varying materials, so the master query will call this subquery to get the total adjustment:

```
select
 ta.Code, 
 ta.Quantity,
 (select tb.cost - tb.NewCost * ta.Quantity ) as BOMEffect 

from BOM TA 
inner join ITEM TC on tC.ItemCode = tA.Father 
inner join NewPriceTable TB on tA.Code = tB.Item 

where TA.Father = '100-01'
and tc.PriceList = 3
order by ta.Father 

```

Which gives me the results (this is all of the materials of one item, quantity of each material, and the price adjustment):

```
Code    Quantity    BOMEffect
D .003  56.000000   -95.08
D .004  28.000000   -62
D .005  20.000000   -54.6
d .006  2.000000    -3.3
D .01   2.000000    -5.5
D .015  4.000000    -25.52
D .02   4.000000    -34

```

All I need from this query is the total BOMEffect (-280). But if I just replace the select statement with:

```
select
 sum((select tb.cost - tb.NewCost * ta.Quantity )) as BOMEffect 

from BOM TA 
inner join ITEM TC on tC.ItemCode = tA.Father 
inner join NewPriceTable TB on tA.Code = tB.Item 

where TA.Father = '100-01'
and tc.PriceList = 3
order by ta.Father 

```

I get:

```
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

```

I know what it's saying, but I don't know any other way to do it. How else can I get this total? This is a small subquery that is part of a larger query, so I only need to return the total. I could probably use a view or a temp table, but I would like to stay away from that if possible.