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!
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
The point was i was using MONTHYEAR alias, and order by PURCHASEDATE... Thanks for detailed answer