Can't use group by in a nested query !!!

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

Can't use group by in a nested query !!!

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-09-03 : 04:31:32

hi there iam getting this error

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

i have a query similar to this ex:

select b.x,b.y,b.z,sum(count)
from (select x,y,z,count(*) as count
group by x,y,z)
group by b.x,b.y,b.z

i also tried to make the nested query as a view then using the group by it gave same error

please help to work around

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-09-03 : 04:49:11

Hi Ann,
When i try the following similar to u r query, there is no error it is working fine
ca u post the exact query


Declare @I Table (Id Int)

Insert Into @I
Select 1 Union ALl
Select 2 Union ALl
Select 2 Union ALl
Select 3 Union ALl
Select 3 Union ALl
Select 3 Union ALl
Select 4 Union ALl
Select 5

Select a.Id, Sum( a.Cnt) From
(
Select Id, Count(Id) as 'cnt'
From @I
Group By Id
) a
Group By  a.Id

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-03 : 04:53:44

Declare @table table
( x int, y int)

Insert into @table
Select 1,67 union all
Select 1,67 union all
Select 5,24 union all
Select 7,8

select * from @table

--select x,y,count(*) as count from @table group by x,y

select tbl.x,tbl.y,sum(count1)
from (select x,y,count(*) as count1 from @table
group by x,y) tbl
group by tbl.x,tbl.y

ann06
Posting Yak Master

171 Posts

Posted - 2008-09-03 : 05:29:30

select branch,branch_name,ishaar_year,year,char_field2_ar,project_name,doc_kind,count
from(
select cast (a.char_field1_ar as int) as branch,p.description as branch_name,a.ishaar_year,t.description as year,a.char_field2_ar,z.description as project_name,a.cat_id,case when a.cat_id='99398' then 'As Built Scanned DWG' when a.cat_id='99406' then 'As Built Soft DWG' else 'others' end as doc_kind,count(*) as count

from technical_main a inner join cust_hierarchy_lookup p on a.char_field1_ar=p.id inner join cust_hierarchy_lookup t on a.ishaar_year=t.id inner join cust_hierarchy_lookup z on a.char_field2_ar=z.id inner join cust_hierarchy_lookup x on x.id=a.cat_id
where p.cabinetid='1' and z.cabinetid ='1'and (t.index_type_id !='project' or t.index_type_id is null) and z.index_type_id !='year' --and a.cat_id in('99398','99406')
group by char_field1_ar,ishaar_year,char_field2_ar,cat_id,p.description,t.description,z.description,x.description
)as B

group by branch,branch_name,ishaar_year,year,char_field2_ar,project_name,doc_kind,sum(count)
order by branch,year,char_field2_ar

the inner query alone works fine when adding the outer query its giving

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

my inner query result
branch branch_name ishaar year char_fld prj_name cat_id doc_kind count
---------------------------------------------------------------------------------------------------------
9 O & M Projects 72361 1427 04 Al Madina Main Palace 70292 others 4
9 O & M Projects 72361 1427 04 Al Madina Main Palace 99355 others 1
9 O & M Projects 72361 1427 04 Al Madina Main Palace 99397 others 1
9 O & M Projects 72361 1427 04 Al Madina Main Palace 99398 As Built Scanned DWG 956
9 O & M Projects 72361 1427 04 Al Madina Main Palace 99406 As Built Soft DWG 101
9 O & M Projects 72361 1427 04 Al Madina Main Palace 99407 others 2
9 O & M Projects 72361 1427 04 Al Madina Main Palace 99408 others 1

i want to some all the all the others into one others row with the sum of the last column (doc kind is cat_id filtered or refined)

any better way to do this query thanks guys

ann06
Posting Yak Master

171 Posts

Posted - 2008-09-03 : 05:35:18

sorry i was having an error in the syntax of my query thanks much after posting i realized the error :S
but i will look at the alternative solutions u provided do they work the same way?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources