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 use (greatly abbreviated) and I keep getting "RVPname is not valid in the select list because it is not contained in the aggregate function or in the GROUP BY clause. Both the DVP name is invalid and the AE name 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'm trying to accomplish.” When LEVEL = 'DVP', ​​I want GROUP BY RVPname, when LEVEL = 'RVP' I want GROUP BY AEname .... does it make sense?

Answer 1

A query group without aggregates (for example, your query) will only be useful for removing duplicates. So your options are to add aggregates for columns in the select clause that are not part of the group, or use the query to remove duplicates.

The following code removes all columns (DVPname, RVPname, AEname, Dealer) that are only conditionally contained in the group statement (it gets into an error if they are not in the group but are in the list). It also adds the name to the group 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 your columns that are not part of the GROUP BY using an aggregate function such as AVG() or SUM() . Otherwise, the database does not know what to do with several records that are returned by grouped records.

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

 SELECT SUM(DVPname), AVG(RVPname), 

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

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

In addition, here is a good format for your group instruction:

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

Answer 3

If you just need a conditional group by , I would suggest using a subquery:

 select name, . .. from (select (CASE WHEN @LEVEL = 'DVP' THEN DVPname WHEN @LEVEL = 'RVP' THEN RVPname WHEN @LEVEL = 'AE' THEN AEname END ) AS NAME, t.* from t ) t group by name; 

However, you will lose all other fields that you want. In most databases, you cannot include columns in select unless they are aggregated or in a group by clause. Here is an alternative to get arbitrary value from columns:

 SELECT min(DVPname), min(RVPname), min(AEname), min(Dealer), min(Product), min(Distribution), . . . from (select (CASE WHEN @LEVEL = 'DVP' THEN DVPname WHEN @LEVEL = 'RVP' THEN RVPname WHEN @LEVEL = 'AE' THEN AEname END ) AS NAME, t.* from t ) t group by name; 

This returns the minimum values ​​for the columns.

Answer 4

I know two methods: GROUP BY (Case case 1, Case statement 2) and a subquery. I determine how many lines each method will take, and then based on this process.

ex. (Group):

  SELECT CASE WHEN (situation A) THEN (result A) WHEN (situation B) THEN (result B) END column1, CASE WHEN (situation C) THEN (result C) WHEN (situation D) THEN (result D) END column2, SUM(AMOUNT) as TotalAMOUNT FROM aTable GROUP BY CASE WHEN (situation A) THEN (result A) WHEN (situation B) THEN (result B) END, CASE WHEN (situation C) THEN (result C) WHEN (situation D) THEN (result D) END 

ex. (Subqueries)

  SELECT Column1, Column2, SUM(AMOUNT) as TotalAMOUNT FROM ( SELECT CASE WHEN (situation A) THEN (result A) WHEN (situation B) THEN (result B) END column1, CASE WHEN (situation C) THEN (result C) WHEN (situation D) THEN (result D) END column2, AMOUNT FROM aTable )CaseTable GROUP BY Column1, Column2 ORDER BY Column1 asc, SUM(AMOUNT) 

I think it is important to remember that you cannot contain additional data beyond what you group together. In the above two examples, the results of your query are grouped by the variable you specify. You can also use a specific type of status depending on your variable, and then drill it with an extra column and not display the last column in the summary.