How to group by month and year?

I have a table PURCHASE with a date column PURCHASEDATE which is in DATE format. I’m trying to get the purchases grouped by month and year. To do so, I try with:

SELECT 
TO_CHAR(PURCHASEDATE, 'MM YYYY') AS MONTHYEAR 
FROM PURCHASE
GROUP BY TO_CHAR(PURCHASEDATE, 'MM YYYY');

I have also tryied with GROUP BY EXTRACT(MONTH FROM PURCHASEDATE), EXTRACT(YEAR FROM PURCHASEDATE) but neither worked.

I’m rusty with SQL querys :S.

EDIT

Table cloumns

Thanks ahead!

Answer

If you have the sample data:

CREATE TABLE purchase ( purchasedate ) AS
SELECT DATE '2019-01-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT DATE '2019-02-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT DATE '2020-01-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT DATE '2020-02-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT DATE '2020-03-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT DATE '2020-04-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 1;

Then, you can use your query:

SELECT TO_CHAR(PURCHASEDATE, 'MM YYYY') AS monthyear,
       COUNT(*) AS frequency
FROM   PURCHASE
GROUP BY
       TO_CHAR(PURCHASEDATE, 'MM YYYY');

Which outputs:

MONTHYEAR | FREQUENCY
:-------- | --------:
03 2020   |         4
01 2019   |         5
01 2020   |         3
02 2020   |         2
02 2019   |         3
04 2020   |         1

Or, you can use TRUNC:

SELECT TRUNC(PURCHASEDATE,'MM') AS monthyear,
       COUNT(*) AS frequency
FROM   PURCHASE
GROUP BY
       TRUNC(PURCHASEDATE,'MM');

Which outputs:

MONTHYEAR           | FREQUENCY
:------------------ | --------:
2020-03-01 00:00:00 |         4
2020-04-01 00:00:00 |         1
2020-02-01 00:00:00 |         2
2020-01-01 00:00:00 |         3
2019-01-01 00:00:00 |         5
2019-02-01 00:00:00 |         3

Or, you can use EXTRACT:

SELECT EXTRACT( YEAR FROM PURCHASEDATE) AS year,
       EXTRACT( MONTH FROM PURCHASEDATE) AS month,
       COUNT(*) AS frequency
FROM   PURCHASE
GROUP BY
       EXTRACT( YEAR FROM PURCHASEDATE),
       EXTRACT( MONTH FROM PURCHASEDATE);

Which outputs:

YEAR | MONTH | FREQUENCY
---: | ----: | --------:
2019 |     1 |         5
2020 |     1 |         3
2020 |     2 |         2
2020 |     4 |         1
2019 |     2 |         3
2020 |     3 |         4

db<>fiddle here