The first section of code is what i used to create a size class report. It works beautifully. However, I need to narrow the focus of this operation but only on CODE values that begin with 11 or 21. Code is a character (6) primary key field, if that helps. The Where statement that I am trying to use is at the bottom. I put it right after the first SELECT statement. I get an incorrect syntax error.
Does it matter where I put the where statement? Second, does the where statement seem correct for what it is intended for?
SELECT
[Areas].[Area],
[classSizes].[sizeclass],
SUM([data].[Employment]) AS [Employment in Size Class],
CASE COUNT([data].[Employment])
WHEN 0 THEN NULL
ELSE COUNT([data].[Employment])
END AS [Number of Sites]
FROM (SELECT DISTINCT
[Area]
FROM [sizeclassreport]) AS [Areas]
CROSS JOIN (SELECT DISTINCT
[sizeclass]
FROM [sizeclassreport]) AS [classSizes]
LEFT OUTER JOIN [sizeclassreport] [data]
ON [Areas].[Area] = [data].[Area]
AND [classSizes].[sizeclass] = [data].[sizeclass]
GROUP BY
[areas].[Area],
[classSizes].[sizeclass]
ORDER BY
[areas].[Area],
[classSizes].[sizeclass]
Where code like '11%' or '21%'
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Where'
In addition to what others have said about the order of the clauses, this is invalid syntax:
Where code like '11%' or '21%'
Instead you need to write:
Where code like '11%' or code like '21%'