I am trying to enable using a variable/parameter along with a 'CASE' expression to determine the "Group By" clause to be used.
I am getting the following error:
Msg 8120, Level 16, State 1, Line 77
Column 'Field_1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The sample SQL I am using is the following:
DECLARE @OrderBy nvarchar(15) = 'Date';
DECLARE @StartDate date = '01/01/2023';
DECLARE @EndDate date = '12/31/2023';
SELECT Field_1, Field_2, Field_3, SUM(Field_4) AS 'Sum'
FROM Table_1
WHERE
Field_2 IN ('A', 'B', 'C', 'D', 'E')
AND Field_3 > 1
AND Field_3 < 20
AND Field_1 >= @StartDate
AND Field_1 <= @EndDate
/*
If @OrderBy = 'Date'
THEN "GROUP BY" should be
GROUP BY Field_1, Field_2, Field_3
IF @OrderBy = 'Location'
THEN "GROUP BY" should be
GROUP BY Field_3, Field_2, Field_1
*/
GROUP BY
CASE WHEN @OrderBy = 'Date'
THEN [Field_1]
END,
CASE WHEN @OrderBy = 'Date'
THEN [Field_2]
END,
CASE WHEN @OrderBy = 'Date'
THEN [Field_3]
END,
CASE WHEN @OrderBy = 'Location'
THEN [Field_2]
END,
CASE WHEN @OrderBy = 'Location'
THEN [Field_3]
END,
CASE WHEN @OrderBy = 'Location'
THEN [Field_1]
END
The idea is that the returned results could be grouped by "Field_1", "Field_2", "Field_3" OR "Field_3", "Field_2", "Field_1" depending on the input parameter @OrderBy.
The goal is got be able to use an input parameter in order to determine the correct "Group By" so that I can have a single stored procedure to handle any case rather than having to have multiple stored procedures.
Anyone have an idea how to make this work?
An order of columns in GROUP BY doesn't matter. Your only want conditional order of columns in ORDER BY
SELECT Field_1, Field_2, Field_3, SUM(Field_4) AS 'Sum'
FROM Table_1
WHERE
Field_2 IN ('A', 'B', 'C', 'D', 'E')
AND
Field_3 > 1
AND
Field_3 < 20
AND
Field_1 >= @StartDate
AND
Field_1 <= @EndDate
GROUP BY
[Field_1],[Field_2], [Field_3]
ORDER BY
CASE @OrderBy
WHEN 'Date' THEN [Field_1]
WHEN 'Location' THEN [Field_2]
END,
CASE @OrderBy
WHEN 'Date' THEN [Field_2]
WHEN 'Location' THEN [Field_3]
END,
CASE @OrderBy
WHEN 'Date' THEN [Field_2]
WHEN 'Location' THEN [Field_1]
END