MySQL Forums
Forum List  »  Newbie

Re-writing sql code using another column to get same result
Posted by: Beethoven Plaisir
Date: August 07, 2018 07:48AM

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...
M M-1 M-3
2 3 2

How can I have the same result (same format) using the column: month
P.S: the datatype of the column month is varchar

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.