sql-servergroup-byparameterscase

Transact SQL, GROUP BY using Case with aggregate function


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?


Solution

  • 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