CODEWITHSUNDEEP
mysqlsql
choding
choding

Reputation: 89

Can I use a column dynamically using CASE WHEN in a GROUP BY query?

The query statement I want is whether GROUP BY can be changed according to the value of the selected column.

For example, The query I was thinking of is as below,

SELECT * FROM (
    SELECT DATE, A,sum(B),C FROM TABLE
    GROUP BY DATE
       CASE WHEN C ='1' THEN A,B
    ELSE '2' THEN A END.
)

If C is 1, the final query is

SELECT DATE, A,sum(B),C FROM TABLE
GROUP BY DATE, A

If C is 2, the final query is

SELECT DATE, A,sum(B),C FROM TABLE
    GROUP BY DATE

If the table has data like below,

DATE     A       B       C
----    ------------------
2022.01 A1       50      1
2022.01 A1       50      1
2022.01 A2       30      1
2022.01 A2       30      1
2022.02 A1       20      2
2022.02 A1       20      2
2022.02 A2       25      2
2022.02 A2       25      2
2022.03 A1       10      1
2022.03 A2       20      1

The query is applied like this according to each C value, so the result I want is

DATE     A           B       C
----    -----------------------
2022.01 A1           100     1
2022.01 A2           60      1
2022.02 (A1+A2)      90      2
2022.03 A1           10      1
2022.03 A2           20      1

want Can you make a sql query with this syntax?

To change and apply the columns grouped according to the C value.. ;(

Upvotes: 0

Views: 454

Answers (1)

Hee Su Chang
Hee Su Chang

Reputation: 116

You might be able to get the result you want by combining the result of two queries using UNION ALL. The exact syntax may differ (sometimes you have to select from a subquery) and you may want to specify ordering but I think this could work.

SELECT DATE,A,B,C 
FROM Table
 WHERE C = '1'
 GROUP BY DATE,A,B
UNION ALL
SELECT DATE,A,B,C 
 FROM Table
 WHERE C = '2'
GROUP BY DATE,A;

EDIT:

You can play around by adding more data and adjust the column types, names, etc. according to your needs. Here's the link to the MySql 5.7 fiddle I used to test the query. https://www.db-fiddle.com/f/gpu8cUo5wRa2Kxn86Ghjde/0

CREATE TABLE ChodingTable(SomeDate DATE, A VARCHAR(2), B INTEGER, C INTEGER);

INSERT INTO ChodingTable 
           (SomeDate,    A,  B,  C)
VALUES ('2022-01-01', 'A1', 50, 1),
       ('2022-01-01', 'A1', 50, 1),
       ('2022-01-02', 'A1', 20, 2),
       ('2022-01-02', 'A2', 20, 2);
       
SELECT SomeDate,GROUP_CONCAT(DISTINCT(A) SEPARATOR '+'),SUM(B),(C)
FROM ChodingTable
  WHERE C=1
  GROUP BY SomeDate,A
UNION ALL
SELECT SomeDate,GROUP_CONCAT(DISTINCT(A) SEPARATOR '+'),SUM(B),C
  FROM ChodingTable
  WHERE C=2
  GROUP BY SomeDate;

Upvotes: 1

Related Questions