sum from a subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum from a subquery

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-11-16 : 07:44:23

Hi,

Thank you in advance for assistance provided.
I have a subquery below

select b.irtbuid,a.*.......
..... and b.year = 2011

From this I trying to derive sum(AOT) grouped by IRTBUID...
but I am receiving
'Incorrect syntax near the keyword 'group'.'

Many thanks

select b.irtbuid,sum(a.aot)
(
SELECT b.IRTBUID,A.* from F_AOT A inner join D_BusinessUnits b
on A.BUKey = b.BUKey
where b.BUKey in (select BUKey from D_BusinessUnits where LEID = '02010'
) and PeriodKey >96 and PeriodKey <=107 AND LedgerKey = 20
and b.Year = 2011
)
group by b.irtbuid

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-11-16 : 08:03:32

select b.irtbuid,sum(a.aot) FROM (
SELECT b.IRTBUID,A.* from F_AOT A inner join D_BusinessUnits b
on A.BUKey = b.BUKey
where b.BUKey in (select BUKey from D_BusinessUnits where LEID = '02010'
) and PeriodKey >96 and PeriodKey <=107 AND LedgerKey = 20
and b.Year = 2011
) abc group by b.irtbuid

--------------------------
http://connectsql.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 09:53:25

why you need derived table? i think below would suffice


SELECT b.IRTBUID,sum(a.aot) 
from F_AOT A 
inner join D_BusinessUnits b 
on A.BUKey = b.BUKey
where b.LEID = '02010'
and PeriodKey >96 and PeriodKey <=107 AND LedgerKey = 20
and b.Year = 2011
group by b.irtbuid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources