August 28, 2012 at 6:22 am
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.06RTMStandard Edition
August 28, 2012 at 6:26 am
You need to group by in the sub select as your using an aggregate function
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) <> 'LG'
GROUP BY
dbo_40.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;
August 28, 2012 at 6:34 am
SELECT
CASE
WHEN (GROUPING([MAT]) = 1) THEN 'Tot'
ELSE [MAT] END
AS MAT,
--[myNUmber]
COUNT (*) AS [myNUmber]
FROM (
SELECT DISTINCT
CASE
WHEN LEFT (TZZ, 2) IN ('1D','1F') THEN 'MAO'
WHEN LEFT (TZZ, 2) IN ('1G','1H') THEN 'MAE'
WHEN LEFT (TZZ, 2) IN ('1I','1M','1S','1O') THEN 'MAC'
WHEN LEFT (TZZ, 2) IN ('1P','1Q','1R') THEN 'MAS'
END AS [MAT]--,
--COUNT (*) AS [myNUmber] -- missing left square bracket, no GROUP BY
FROM dbo_40
WHERE 1 = 1
AND LEFT (TZZ, 2) <> 'LG' --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;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 28, 2012 at 6:41 am
ChrisM@Work (8/28/2012)
SELECTCASE
WHEN (GROUPING([MAT]) = 1) THEN 'Tot'
ELSE [MAT] END
AS MAT,
--[myNUmber]
COUNT (*) AS [myNUmber]
FROM (
SELECT DISTINCT
CASE
WHEN LEFT (TZZ, 2) IN ('1D','1F') THEN 'MAO'
WHEN LEFT (TZZ, 2) IN ('1G','1H') THEN 'MAE'
WHEN LEFT (TZZ, 2) IN ('1I','1M','1S','1O') THEN 'MAC'
WHEN LEFT (TZZ, 2) IN ('1P','1Q','1R') THEN 'MAS'
END AS [MAT]--,
--COUNT (*) AS [myNUmber] -- missing left square bracket, no GROUP BY
FROM dbo_40
WHERE 1 = 1
AND LEFT (TZZ, 2) <> 'LG' --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;
Thanks a lot! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply