Thanks Stuart.

I have edited my original post as I mixed up my temp table name with my SQL query so far. Sorry for that.

Basically I want to multiply test by duration, sum all of these results, then divide by the sum of duration. But I need to group the results into ID and layer (ID and layer define one piece of equipment.)

This may explain:

SELECT ID,layer,SUM(test*duration)/SUM(duration)

FROM test

WHERE ID = 11 and layer = 1

GROUP BY ID, layer

SELECT ID,layer,SUM(test*duration)/SUM(duration)

FROM test

WHERE ID = 11 and layer = 2

GROUP BY ID, layer

etc WHERE ID ==11 and layer = 3...........

and so on

But of course I want the result in one table for every combination of [ID] and [layer] that will exist in the real large table