select x.id, x.value1, sum(y.value2), x.value1 - sum(y.value2)
from x
inner join y
on x.id = y.idx
group by
x.id, x.value1
Having the subquery in the SELECT caluse, as you have done it, would probably have the subquery execute for every record returned and therefore would be slower. The SQL Engine might be smart enough to figure out what you have done and fix things under the covers but it might not.
================================================= We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
but as i use inner join then in output i do not have line 4 coouse it do not have any records in Y table. and i neeed to have all records from table X despite of any records for it in Y.
Select x.id,x.Value1,YSUM,ISNULL(Value1,0) - YSUM
from x
inner join
(
Select x.id,SUM(isnull(y.value2,0))YSUM
from x
left join y on x.id = y.idx
group by x.id
)y on x.id = y.id
SELECT m.id,m.value1,COALESCE(n.Tot,0) AS Tot,m.value1-COALESCE(n.Tot,0) AS Res
FROM x m
LEFT JOIN (SELECT idx,SUM(value2) AS Tot
FROM y
GROUP BY idx
)n
ON n.idx = m.id
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Basic join or select question
Posted - 11/12/2012 : 17:15:13
