sqloracle-databasedateselectgroup-by

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 query.

EDIT

Table cloumns

enter image description here

Thanks ahead!


Solution

  • 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