I am writing a query to return an resultset as follows:
select Weldment = p.partName,
NbrProduced = sum(p.qtyExpected),
Batches = sum(1),
AvgWeldTime = AVG(DATEDIFF(hh,
(SELECT TOP 1 transactiontime trans
FROM vtransaction t2
WHERE t2.partid = t.partid AND t2.transactiontype = 9 and t2.parkinglotid=@lotid
ORDER BY transactionTime DESC),
from vtransaction t
left join vparts p on t.partId = p.id
where t.parkinglotid in (select downstreamid from vparkinglotdependencies where parkinglotid = @lotid)
and t.transactiontime < @dateto
and t.transactiontime > @datefrom
and t.transactionType = 8
and p.partname is not null
and p.parttype = 'weldment'
group by p.partName
I want to calculate the number of parts produced and the average time between two types of transactions (transactionType 8 and 9) for each part number (p.partName). Well it doesn't like AvgWeldTime = .... and gives me the error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
How can I accomplish this with a single statement?