SQL Group By Query

Question

I have the following table with one column ArbPlWPos:

+------------+
+ IH-MKE     +
+ IH-MKEEA   +
+ IH-MKEEB   +
+ IH-MKEPE   +
+ IH-MKEPM   +
+ IH-MVKE1   +
+ IH-MVKM1   +
+------------+

I can run a statement in MS Access which groups by the first 6 letters:

SELECT left(ArbPlWPos, 6), count(left(ArbPlWPos, 6))
  FROM my_table
 GROUP BY left(ArbPlWPos, 6)

+------------+------+
+ IH-MKE     +  10  +
+ IH-MKM     +  20  +
+ IH-MVK     +  30  +
+------------+------+

How to include the IH-MVK into the IH-MKE, so the result should be:

+------------+------+
+ IH-MKE     +  40  +
+ IH-MKM     +  20  +
+------------+------+

Is this somehow possible with SQL/Access?

Answer 1

In MS Access, you can do this using a conditional expression, iif():

SELECT iif(ArbPlWPos like "IH-MVK*", "IH-MKE", left(ArbPlWPos, 6)),
       count(*)
FROM TABLE
GROUP BY iif(ArbPlWPos like "IH-MVK*", "IH-MKE", left(ArbPlWPos, 6));

Answer 2

You can group by any expression, but you are to repeat it after SELECT (is you need) and after GROUP BY as you did with an usual column. For example:

  SELECT my_function_or_expression(column_A, column_B), count(1)
    FROM my_table
  GROUP BY my_function_or_expression(column_A, column_B);

In your case it will be:

  SELECT 
     CASE WHEN left(ArbPlWPos, 6) = 'IH-MVK'
          THEN 'IH-MKE'
          ELSE left(ArbPlWPos, 6) END AS cutArbPlWPos,
     count(1) AS amount
    FROM my_table
  GROUP BY CASE WHEN left(ArbPlWPos, 6) = 'IH-MVK'
                THEN 'IH-MKE'
                ELSE left(ArbPlWPos, 6) END;

You can also simplify it using subquery.

SELECT cutArbPlWPos, count(1)
  FROM (SELECT 
         CASE WHEN left(ArbPlWPos, 6) = 'IH-MVK'
              THEN 'IH-MKE'
              ELSE left(ArbPlWPos, 6) END AS cutArbPlWPos
        FROM my_table
       )
 GROUP BY cutArbPlWPos;