invalid colom name due nested aggration

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

invalid colom name due nested aggration

Author

Topic

Cartier
Starting Member

2 Posts

Posted - 2012-05-18 : 06:28:56

Hi Guys,

I've Created a nested aggregation. However When I run the query in Microsoft SQL express, I get the error below:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'shpmyear'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'shpmarea'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'shpmyear'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'shpmarea'.

This is my query:

Select
shipmentyear,
shipmentarea,
avg(System.shpmgwgt)
from (Select count(shipmentwgt) AS shpmgwgt
from dbo.System
where shpmyear>=2012) as System
Group by shpmyear, shpmarea

I'm 100% sure that the colom names are correct.

I tried it using it the qeury without the nested aggeretation, I get the right results.

Select shipmentyear, shipmentarea
From dbo.System
Where shipmentyear=>=2012

Hope some one can help!

Cheers

iSam

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-05-18 : 06:51:12

What i suggest you to is select shpmyear and shpmarea in your Sub-Query.
I think modifying your sub-query in following manner will help you.

Select count(shipmentwgt) AS shpmgwgt,shpmyear, shpmarea
from dbo.System
where shpmyear>=2012
GROUP BY shpmyear, shpmarea

------------------------------------------------
The answer is always no till than you don't ask.

Cartier
Starting Member

2 Posts

Posted - 2012-05-19 : 12:33:49

I will give it at try and let you know ASAP. Thanks!

quote:
Originally posted by ashishashish

What i suggest you to is select shpmyear and shpmarea in your Sub-Query.
I think modifying your sub-query in following manner will help you.

Select count(shipmentwgt) AS shpmgwgt,shpmyear, shpmarea
from dbo.System
where shpmyear>=2012
GROUP BY shpmyear, shpmarea

------------------------------------------------
The answer is always no till than you don't ask.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources