Question 22074 in Mysql Question posted in
The official MySQL documentation can be found here.

Optimize the usage of CASE statement in MySQL

enter image description here

Hi all,

I have a table as shown in the screenshot above. I wrote a query using CASE statement so that it will return extra columns that I need. Below is the query that I wrote:

SELECT 
    *,
    CASE WHEN (SUM(CASE WHEN class = 'class 1' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 1',
    CASE WHEN (SUM(CASE WHEN class = 'class 2' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 2',
    CASE WHEN (SUM(CASE WHEN class = 'class 3' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 3',
    CASE WHEN (SUM(CASE WHEN class = 'class 4' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 4'
FROM qa;

This is the result table that I get:

enter image description here

What I want to achieve in this query is, if the student attended the class, it will show 1 under the column of the class for all the rows belong to that student.

For example, student with student_id 2 attended class 1, so under Class 1 column, both rows for student_id 2 will show 1.

I already achieved what I want in my query, but right now instead of using 1, I want it to be the enrollment_date of the class. Below is the final output that I want:

enter image description here

May I know how should I modify my query to get the final output in the screenshot above?

2nd Question:

As you can see in my query above, every class is having one CASE statement respectively in order to create the column for the class. However, there might be Class 5,6,7,… in future, so I need to add in the extra CASE statement again whenever there is different new class exist. Is there anyway that I can optimize my query so that there is no need to have 4 CASE statement for 4 different classes, and yet still can create columns for different classes (when there is a new class, there will be new column for the class as well)?

Sample Data

create table qa(
    student_id INT,
    class varchar(20),
    class_end_date date,
    enrollment_date date
);

insert into qa (student_id, class, class_end_date, enrollment_date) 
values 
(1, 'class 1', '2022-03-03', '2022-02-14'),
(1, 'class 3', '2022-06-13', '2022-04-12'),
(1, 'class 4', '2022-07-03', '2022-06-19'),
(2, 'class 1', '2023-03-03', '2022-07-14'),
(2, 'class 2', '2022-08-03', '2022-07-17'),
(4, 'class 4', '2023-03-03', '2022-012-14'),
(4, 'class 2', '2022-04-03', '2022-03-21')
;
Tags:

2

Answers


  1. 0

    Here’s an example with both class_end_date and enrollment_date pivoted –

    SELECT 
    student_id,
    GROUP_CONCAT(IF(class = 'class 1', enrollment_date, null)) 'Class 1 Enrolled',
    GROUP_CONCAT(IF(class = 'class 1', class_end_date, null)) 'Class 1 End',
    GROUP_CONCAT(IF(class = 'class 2', enrollment_date, null)) 'Class 2 Enrolled',
    GROUP_CONCAT(IF(class = 'class 2', class_end_date, null)) 'Class 2 End',
    GROUP_CONCAT(IF(class = 'class 3', enrollment_date, null)) 'Class 3 Enrolled',
    GROUP_CONCAT(IF(class = 'class 3', class_end_date, null)) 'Class 3 End',
    GROUP_CONCAT(IF(class = 'class 4', enrollment_date, null)) 'Class 4 Enrolled',
    GROUP_CONCAT(IF(class = 'class 4', class_end_date, null)) 'Class 4 End'
FROM qa
GROUP BY student_id;
    Login or Signup to reply.
  2. 0

    See Pivot for a Stored Procedure that will generate and [optionally] run a query based on the table definition and data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search