SQL: Group By with Case Statement for multiple fields

Question

I am trying to write a GROUP BY clause with a CASE statement so I can conditionally GROUP BY according to the value of the parameter in my query. Here is my query that I am using (significantly shortened) and I keep getting "RVPname is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And DVPname is invalid and AEname is invalid.

SELECT
DVPname, RVPname, AEname, Dealer, Product, Distribution, 

CASE WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL = 'RVP' THEN RVPname
WHEN @LEVEL = 'AE' THEN AEname
END AS NAME

..........other code here.....

GROUP BY Product, Distribution,
CASE WHEN @LEVEL = 'RVP' THEN AEname WHEN @LEVEL = 'DVP' THEN RVPname WHEN @LEVEL= 'AE' THEN Dealer END

--Does anyone know how to do what I am trying to accomplish. When LEVEL='DVP' I want to GROUP BY RVPname, when LEVEL='RVP' I want to GROUP BY AEname....make sense?

Answer 1

A group by query without aggregates (such as your query) will only be useful for removing duplicates. So your options are to add aggregates for columns in the select clause that aren't in the group by or use the query to remove duplicates.

The following code removes all of the columns (DVPname, RVPname, AEname, Dealer) that are only conditionally in the group by statement (it will hit an error whenever they are not in the group by but are in the select). It also adds the name to the group by so that it can be in the select clause.

SELECT
Product, Distribution,
CASE 
    WHEN @LEVEL = 'DVP' THEN DVPname
    WHEN @LEVEL = 'RVP' THEN RVPname
    WHEN @LEVEL = 'AE' THEN AEname
END AS NAME,
CASE 
     WHEN @LEVEL = 'RVP' THEN AEname 
     WHEN @LEVEL = 'DVP' THEN DVPname 
     WHEN @LEVEL= 'AE' THEN Dealer 
END 

..........other code here.....

GROUP BY Product, Distribution, 
CASE 
    WHEN @LEVEL = 'DVP' THEN DVPname
    WHEN @LEVEL = 'RVP' THEN RVPname
    WHEN @LEVEL = 'AE' THEN AEname
END,
CASE 
     WHEN @LEVEL = 'RVP' THEN AEname 
     WHEN @LEVEL = 'DVP' THEN DVPname 
     WHEN @LEVEL= 'AE' THEN Dealer 
END 

Answer 2

You will need to select all of your columns that are not in the GROUP BY clause with an aggregate function such as AVG() or SUM(). Otherwise, the database doesn't know what to do with the multiple entries that are returned with grouped records.

For example, your select statement should start out with something like this:

SELECT
SUM(DVPname), AVG(RVPname),

If they are text entries and you know they are all the same, you can also add they to the GROUP BY clause.

GROUP BY DVPname, RVPname, AEname, Dealer, Product, Distribution

In addition, here is a good format for your group by case statement:

GROUP BY
CASE WHEN @SortColumn = '0'
THEN [id] END,
CASE WHEN @SortColumn = '1'
THEN [name] END;