[Err] 42000 - [SQL Server] Column 'dbo_40.TZZ'

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 2008 Forums

Transact-SQL (2008)

[Err] 42000 - [SQL Server] Column 'dbo_40.TZZ'

Author

Topic

cms9651
Starting Member

28 Posts

Posted - 2012-08-28 : 08:20:12

Hi all, I need your help.

I try this query but I have this error, why?

Can you help me?
Thanks in advance.

[Err] 42000 - [SQL Server] Column 'dbo_40.TZZ' is invalid in the select list 
because it is not contained in either an aggregate function or the GROUP BY clause.



SELECT
	(
		CASE
		WHEN (GROUPING([MAT]) = 1) THEN
			'Tot'
		ELSE
			[MAT]
		END
	) AS MAT,
	[myNUmber]
	
FROM
	(
		SELECT DISTINCT
			CASE
		WHEN LEFT (TZZ, 2) = '1D'
		OR LEFT (TZZ, 2) = '1F' THEN
			'MAO'
		WHEN LEFT (TZZ, 2) = '1G'
		OR LEFT (TZZ, 2) = '1H' THEN
			'MAE'
		WHEN LEFT (TZZ, 2) = '1I'
		OR LEFT (TZZ, 2) = '1M'
		OR LEFT (TZZ, 2) = '1S'
		OR LEFT (TZZ, 2) = '1O' THEN
			'MAC'
		WHEN LEFT (TZZ, 2) = '1P'
		OR LEFT (TZZ, 2) = '1Q'
		OR LEFT (TZZ, 2) = '1R' THEN
			'MAS'
		END AS [MAT],
		COUNT (*) AS [myNUmber]		
	FROM
		dbo_40
	WHERE
		1 = 1
	AND LEFT (TZZ, 2) NOT LIKE 'LG%'
	) AS SubQ
WHERE
	1 = 1
GROUP BY
	[MAT] WITH ROLLUP
ORDER BY
	CASE [MAT]
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
ELSE
	4
END;

Version SQL server:

9.00.1399.06	RTM	Standard Edition

Andy Hyslop
Starting Member

14 Posts

Posted - 2012-08-28 : 08:35:10

Try this:


SELECT
	(
		CASE
		WHEN (GROUPING([MAT]) = 1) THEN
			'Tot'
		ELSE
			[MAT]
		END
	) AS MAT,
	[myNUmber]
	
FROM
	(
		SELECT DISTINCT
			CASE
		WHEN LEFT (TZZ, 2) = '1D'
		OR LEFT (TZZ, 2) = '1F' THEN
			'MAO'
		WHEN LEFT (TZZ, 2) = '1G'
		OR LEFT (TZZ, 2) = '1H' THEN
			'MAE'
		WHEN LEFT (TZZ, 2) = '1I'
		OR LEFT (TZZ, 2) = '1M'
		OR LEFT (TZZ, 2) = '1S'
		OR LEFT (TZZ, 2) = '1O' THEN
			'MAC'
		WHEN LEFT (TZZ, 2) = '1P'
		OR LEFT (TZZ, 2) = '1Q'
		OR LEFT (TZZ, 2) = '1R' THEN
			'MAS'
		END AS [MAT],
		COUNT (*) AS [myNUmber]		
	FROM
		dbo_40
	WHERE
		1 = 1
	AND LEFT (TZZ, 2) NOT LIKE 'LG%'
	
	GROUP BY /*Here*/
	TZZ
	
	) AS SubQ
WHERE
	1 = 1
GROUP BY
	[MAT] WITH ROLLUP
ORDER BY
	CASE [MAT]
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
ELSE
	4
END;

Andy

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-28 : 08:36:08

SELECT ( CASE WHEN ( GROUPING([MAT]) = 1 ) THEN 'Tot'
ELSE [MAT]
END ) AS MAT, [myNUmber]
FROM ( SELECT DISTINCT
CASE WHEN LEFT(TZZ, 2) = '1D'
OR LEFT(TZZ, 2) = '1F' THEN 'MAO'
WHEN LEFT(TZZ, 2) = '1G'
OR LEFT(TZZ, 2) = '1H' THEN 'MAE'
WHEN LEFT(TZZ, 2) = '1I'
OR LEFT(TZZ, 2) = '1M'
OR LEFT(TZZ, 2) = '1S'
OR LEFT(TZZ, 2) = '1O' THEN 'MAC'
WHEN LEFT(TZZ, 2) = '1P'
OR LEFT(TZZ, 2) = '1Q'
OR LEFT(TZZ, 2) = '1R' THEN 'MAS'
END AS [MAT], COUNT(*) AS [myNUmber]
FROM dbo_40
WHERE 1 = 1
AND LEFT(TZZ, 2) NOT LIKE 'LG%'

GROUP BY CASE WHEN LEFT(TZZ, 2) = '1D'
OR LEFT(TZZ, 2) = '1F' THEN 'MAO'
WHEN LEFT(TZZ, 2) = '1G'
OR LEFT(TZZ, 2) = '1H' THEN 'MAE'
WHEN LEFT(TZZ, 2) = '1I'
OR LEFT(TZZ, 2) = '1M'
OR LEFT(TZZ, 2) = '1S'
OR LEFT(TZZ, 2) = '1O' THEN 'MAC'
WHEN LEFT(TZZ, 2) = '1P'
OR LEFT(TZZ, 2) = '1Q'
OR LEFT(TZZ, 2) = '1R' THEN 'MAS'
END ) AS SubQ
WHERE 1 = 1
GROUP BY [MAT]
WITH ROLLUP
ORDER BY CASE [MAT]
WHEN 'MAO' THEN 1
WHEN 'MAE' THEN 2
WHEN 'MAC' THEN 3
ELSE 4
END ;

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

cms9651
Starting Member

28 Posts

Posted - 2012-08-28 : 09:03:29

thanks for reply, but if add another alias in my query I have the same error for new alias:


SELECT
	(
		CASE
		WHEN (GROUPING([MAT]) = 1) THEN
			'Tot'
		ELSE
			[MAT]
		END
	) AS MAT,
	[myNUmber],
        [NewAlias]
	
FROM
	(
		SELECT 
			CASE
		WHEN LEFT (TZZ, 2) = '1D'
		OR LEFT (TZZ, 2) = '1F' THEN
			'MAO'
		WHEN LEFT (TZZ, 2) = '1G'
		OR LEFT (TZZ, 2) = '1H' THEN
			'MAE'
		WHEN LEFT (TZZ, 2) = '1I'
		OR LEFT (TZZ, 2) = '1M'
		OR LEFT (TZZ, 2) = '1S'
		OR LEFT (TZZ, 2) = '1O' THEN
			'MAC'
		WHEN LEFT (TZZ, 2) = '1P'
		OR LEFT (TZZ, 2) = '1Q'
		OR LEFT (TZZ, 2) = '1R' THEN
			'MAS'
		END AS [MAT],
		COUNT (*) AS myNUmber],
		SUM (
			CASE
			WHEN (
				[R] = 'D'
				OR [R] = '2'
				OR [R] = '3'
				OR [R] = '4'
			)
			AND [myDate] IS NOT NULL THEN
				1
			ELSE
				0
			END
		) AS [NewAlias]            	
	FROM
		dbo_40
	WHERE
		1 = 1
	AND LEFT (TZZ, 2) NOT LIKE 'LG%'

GROUP BY CASE WHEN LEFT(TZZ, 2) = '1D'
OR LEFT(TZZ, 2) = '1F' THEN 'MAO'
WHEN LEFT(TZZ, 2) = '1G'
OR LEFT(TZZ, 2) = '1H' THEN 'MAE'
WHEN LEFT(TZZ, 2) = '1I'
OR LEFT(TZZ, 2) = '1M'
OR LEFT(TZZ, 2) = '1S'
OR LEFT(TZZ, 2) = '1O' THEN 'MAC'
WHEN LEFT(TZZ, 2) = '1P'
OR LEFT(TZZ, 2) = '1Q'
OR LEFT(TZZ, 2) = '1R' THEN 'MAS'
END

	) AS SubQ
WHERE
	1 = 1
GROUP BY
	[MAT] WITH ROLLUP
ORDER BY
	CASE [MAT]
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
ELSE
	4
END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources