Mysql Query / Stored procedure to fetch data column wise based on range of month selected

Question

I select month and year with range Jan-23 to March 23. The output expected should be as shown below using mysql query or stored procedure.

	Jan-23 Present	Jan-23Absent	Feb-23Present	Feb-23Absent
Class A	22	23	10	30
Class B	22	23	10	30
Class C	15	2	12	35

I tried using the below query:

SELECT class  as 'Class', 
  sum(if(Attendance='Present',1,0))as 'Present', 
  sum(if(Attendance='Absent',1,0))as 'Absent' 
FROM attendance_data  
where date(attendance_date) between '2023-09-01' and '2023-11-01'  
group by class

This resulted in:

	Present	Absent
Class A	1	2
Class B	3	4

I need the solution at the very earliest. Thank you.

Answer 1

You should simply be able to add another condition inside your conditional sum, and use the YEAR() and MONTH() functions to check the date:

SELECT class AS 'Class', 
  SUM(Attendance = 'Present' AND YEAR(attendance_date) = 2023 AND MONTH(attendance_date) = 1) AS 'Jan-23Present', 
  SUM(Attendance = 'Absent' AND YEAR(attendance_date) = 2023 AND MONTH(attendance_date) = 1) AS 'Jan-23Absent', 
  SUM(Attendance = 'Present' AND YEAR(attendance_date) = 2023 AND MONTH(attendance_date) = 2) AS 'Feb-23Present', 
  SUM(Attendance = 'Absent' AND YEAR(attendance_date) = 2023 AND MONTH(attendance_date) = 2) AS 'Feb-23Absent'
FROM attendance_data  
WHERE DATE(attendance_date) BETWEEN '2023-01-01' AND '2023-02-28'  
GROUP BY class

If you want to make the generation of columns dynamic according to the date range, then you can use a recursive CTE to generate the statement:

    WITH RECURSIVE months (d) AS (
        SELECT CAST(CONCAT(SUBSTR(fromDate, 1, 7), '-01') AS DATE)
        UNION ALL
        SELECT d + INTERVAL 1 MONTH
        FROM months
        WHERE d + INTERVAL 1 MONTH <= toDate
    ) SELECT
        CONCAT(
            'SELECT class AS "Class",',
            GROUP_CONCAT(
                CONCAT_WS(',',
                    CONCAT('SUM(Attendance = "Present" AND YEAR(attendance_date) = ', YEAR(d), ' AND MONTH(attendance_date) = ', MONTH(d), ') AS "', DATE_FORMAT(d, '%b-%y'), 'Present"'),
                    CONCAT('SUM(Attendance = "Absent" AND YEAR(attendance_date) = ', YEAR(d), ' AND MONTH(attendance_date) = ', MONTH(d), ') AS "', DATE_FORMAT(d, '%b-%y'), 'Absent"')
                )
            ),
            '
            FROM attendance_data
            WHERE DATE(attendance_date) BETWEEN "', fromDate, '" AND "', toDate, '"
            GROUP BY class'
        )
    FROM months

The CTE at the top just generates a list of all the months between fromDate and toDate, and the SELECT part then uses this list to construct a new SQL statement using these months.

Putting this all together, you could call this from within a stored procedure like this:

DELIMITER //

CREATE PROCEDURE myproc(IN fromDate DATE, IN toDate DATE)
BEGIN
    SET @s := (
        WITH RECURSIVE months (d) AS (
            SELECT CAST(CONCAT(SUBSTR(fromDate, 1, 7), '-01') AS DATE)
            UNION ALL
            SELECT d + INTERVAL 1 MONTH
            FROM months
            WHERE d + INTERVAL 1 MONTH <= toDate
        ) SELECT
            CONCAT(
                'SELECT class AS "Class",',
                GROUP_CONCAT(
                    CONCAT_WS(',',
                        CONCAT('SUM(Attendance = "Present" AND YEAR(attendance_date) = ', YEAR(d), ' AND MONTH(attendance_date) = ', MONTH(d), ') AS "', DATE_FORMAT(d, '%b-%y'), 'Present"'),
                        CONCAT('SUM(Attendance = "Absent" AND YEAR(attendance_date) = ', YEAR(d), ' AND MONTH(attendance_date) = ', MONTH(d), ') AS "', DATE_FORMAT(d, '%b-%y'), 'Absent"')
                    )
                ),
                '
                FROM attendance_data
                WHERE DATE(attendance_date) BETWEEN "', fromDate, '" AND "', toDate, '"
                GROUP BY class'
            )
        FROM months
    );

    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END
//

DELIMITER ;

You could call this stored procedure like this:

CALL myproc('2023-01-01', '2023-03-01');

This approach is a bit fiddly - arguably this kind of logic might better be done in your application layer. However, this should give you the result you're looking for.