Each GROUP BY expression must contain at least one

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

SQL Server 2005 Forums

Transact-SQL (2005)

Each GROUP BY expression must contain at least one

Author

Topic

bunny28
Starting Member

13 Posts

Posted - 2010-01-20 : 04:01:02

hi,

i got this error message when i am trying to create a sp with the following statement

Error Message: Msg 164, Level 15, State 1, Procedure Inventory_Report, Line 9
Each GROUP BY expression must contain at least one column that is not an outer reference.

create proc [dbo].[Inventory_Report]

( @sw as varchar(100))
as

begin

select count(*) nooflicences,entity,@sw from inventorylist where @sw is not null
group by entity,@sw order by entity

end

can any one help me how to overcome this error.
i want use the result to design a crystal report.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 04:04:20

I don;t think you need @SQ in your GROUP BY clause, as it is a constant.

bunny28
Starting Member

13 Posts

Posted - 2010-01-20 : 04:19:51

my query is like this

select count(*) nooflicences,entity,autocad from inventorylist where autocad is not null
group by entity,autocad order by entity

i want the same result using sp.
as autocad is not constant i want to pass this value to sp.

if i take out that from the group by clause, result i am getting is different from the statement.

Regards

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 05:31:02

So you want @sw to contain a column name?

If you do WHERE @sw IS NULL that checks if the value contained in the variable @sw is null - if @sw happens to contain a column name the test does NOT check the value of that column, only the contents of @sw.

If you want @sw to contain a column name instead, then you will have to use dynamic SQL, you can't do it as a "generic SProc" in the way you currently have it, I'm afraid.

bunny28
Starting Member

13 Posts

Posted - 2010-01-20 : 20:57:48

thanks Kristen

i agree with u.
but i am finding it hard to send a column name dynamically
can u please help me out.

Regards

bunny28
Starting Member

13 Posts

Posted - 2010-01-21 : 00:46:54

hi
i am trying in this way still i am getting errors

@sw as varchar(100)
AS
Declare @SQLQry as varchar(500)

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF

Begin

set @SQLQry = "select count(*) cnt,entity," +@sw
set @SQLQry = @SQLQry + " from inventorylist where " +@sw
set @SQLQry = @SQLQry + " is not null "
set @SQLQry = @SQLQry + " group by entity," +@sw
set @SQLQry = @SQLQry + " order by entity "

print @SQLQry

end

please correct me if i am wrong.

regards

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 02:03:58

Looks alright to me, you missed off the CREATE PROCEDURE bit at the top, and there is no "AS" in the parameter definition:


CREATE PROCEDURE dbo.MyTempSproc
	@sw varchar(100)
AS
Declare @SQLQry varchar(500)

SET @sw = 'autocad'
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF

Begin

set @SQLQry = "select count(*) cnt,entity," +@sw
set @SQLQry = @SQLQry + " from inventorylist where " +@sw
set @SQLQry = @SQLQry + " is not null "
set @SQLQry = @SQLQry + " group by entity," +@sw
set @SQLQry = @SQLQry + " order by entity "

print @SQLQry

end

EXEC dbo.MyTempSproc @sw='AUTOCAD'

gives me:


select count(*) cnt,entity,autocad 
from inventorylist 
where autocad is not null  
group by entity,autocad 
order by entity

which is what I think you want?

When you are happy with the SQL that you are producing you can replace the PRINT with
EXEC (@SQLQry)

Note that the user will HAVE to have SELECT permission on the inventorylist, rather than just EXECUTE permissions on the Procedure, which is a downside of using dynamic SQL.

bunny28
Starting Member

13 Posts

Posted - 2010-01-21 : 02:37:56

thanks for the reply sorry to bother u agian

when i tried to create procedure it gave me errors so i changed all the "double" quotes to 'single' quote then only i could able to create.

when i execute it with the parameter it was executed very well and printed statement was absolutely correct.

then i changed print to exec and executed again.

i am sorry this time this is the error message.

Msg 2812, Level 16, State 62, Procedure Inventory_Report, Line 22
Could not find stored procedure 'select count(*) cnt,entity,autocad from inventorylist where autocad is not null group by entity,autocad order by entity '.

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:12:16

Have you got parentehsis around the EXEC as per my example above, i.e.

EXEC (@SQLQry)

bunny28
Starting Member

13 Posts

Posted - 2010-01-21 : 03:16:38

hey i am sorry now got the result.

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources