CODEWITHSUNDEEP
sqlsql-server-2008
Obfuscated
Obfuscated

Reputation: 321

sum a subquery giving error

I have looked through the other titles and can see some of what may be needed, but can't make this work. Here is my query...

SELECT s.RepairCode

,sum((SELECT isnull (Sum(Convert(numeric(8,2), ((tp2.FootWeight/12)*(FinishLength)/2000))),0)
FROM (NYS2Reheat r2 INNER JOIN NYS2SawPieces s2 ON r2.recordId = s2.rollrecID)
left join TensileProducts tp2 on r2.FinalProd = tp2.sqlproduct
where (s2.recordid = s.recordid) and tp2.Active = 1)) as tons

FROM (NYS2Reheat r INNER JOIN NYS2SawPieces s ON r.recordId = s.rollrecID)
left join TensileProducts tp on r.FinalProd = tp.sqlproduct
WHERE r.RollShiftID like '07/17/1525%A' and s.RepairCode like '%F%'
order by s.repaircode

I get the "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." error. How can I sum the sub-select? By the way, this query returns (without the sum)...

F   3.22
F   3.22
F   2.23
F   3.96

I'm looking for F 12.63

Thanks,

Upvotes: 0

Views: 35

Answers (1)

Holmes IV
Holmes IV

Reputation: 1739

I think you just need to set the sum, into a sub query and join it to the other query. I think this would work, but I do not have the tables to test.

SELECT s.RepairCode
,sum(tot.totals) as tons
FROM NYS2Reheat r 
INNER JOIN NYS2SawPieces s ON r.recordId = s.rollrecID
left join TensileProducts tp on r.FinalProd = tp.sqlproduct
Left JOIN (SELECT s.REpairCode, isnull (Sum(Convert(numeric(8,2), ((tp2.FootWeight/12)*(FinishLength)/2000))),0) 'totals'
FROM NYS2Reheat r2 
INNER JOIN NYS2SawPieces s2 ON r2.recordId = s2.rollrecID
left join TensileProducts tp2 on r2.FinalProd = tp2.sqlproduct
where s2.recordid = s.recordid and tp2.Active = 1
Group by s.RepairCode) tot
on tot.repaidcode = s.RepairCode

WHERE r.RollShiftID like '07/17/1525%A' and s.RepairCode like '%F%'
group by s.RepairCode
order by s.repaircode

Upvotes: 1

Related Questions