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 query.
EDIT
Table cloumns
Thanks ahead!
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