0

Sample Data

TRAINING_DATE    GENDER
AUG-18             M
AUG-18             F
SEPT-18            F
OCT-18             M
OCT-18             M
NOV-18             M
NOV-18             F

Expected Output. Number of people trained between SEPT-18 and NOV-18

   F      M
   2      3
1

You should store your data differently. Storing year and month separately might be a good idea. You can do that like this:

ALTER TABLE your_table ADD COLUMN training_year int, ADD COLUMN training_month tinyint;

UPDATE your_table SET 
training_year = YEAR(STR_TO_DATE(TRAINING_DATE, '%b-%y')),
training_month = MONTH(STR_TO_DATE(TRAINING_DATE, '%b-%y'));

When you don't need your TRAINING_DATE column anymore, you can drop it.

Now you could query your table like this:

SELECT gender,
       COUNT(*)
FROM your_table
WHERE training_year = 2018
  AND training_month BETWEEN 9 AND 11
GROUP BY gender;
  • read more about the STR_TO_DATE() function and some more here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.