Supposed the following scenario
CREATE TABLE card (date_field, numcard, month);
INSERT INTO card (date_filed, numcard, month)
VALUES
(2018-06-01, 12531, June-2018),
(2018-06-02, 29182, June-2018),
(2018-05-01, 12781, May-2018),
(2018-05-29, 56171, May-2018),
(2018-05-10, 27191, May-2108),
(2018-04-10, 83231, April-2018),
(2018-03-01, 31131, March-2018),
(2018-03-02, 47131, March-2018),
(2018-02-15, 34617, February-2018);
Using the column date_field in the following SQL code (MySQL)...
SELECT count(CASE
WHEN date_field >= current_date - interval 2 month - interval dayofmonth(current_date) - 1 day
AND date_field < current_date - interval 1 month - interval dayofmonth(current_date) - 1 day THEN
1
END) `M`,
count(CASE
WHEN date_field >= current_date - interval 3 month - interval dayofmonth(current_date) - 1 day
AND date_field < current_date - interval 2 month - interval dayofmonth(current_date) - 1 day THEN
1
END) `M-1`,
count(CASE
WHEN date_field >= current_date - interval 4 month - interval dayofmonth(current_date) - 1 day
AND date_field < current_date - interval 3 month - interval dayofmonth(current_date) - 1 day THEN
1
END) `M-3`,
FROM my_table
WHERE date_field >= current_date - interval 4 month - interval dayofmonth(current_date) - 1 day
AND date_field < current_date - interval 1 month - interval dayofmonth(current_date) - 1 day;
I have this result...
How can I have the same result (same format) using the column: month
P.S: the datatype of the column month is varchar