Search code examples
sqlmysqloracle-databasegrouping

How do I get the desired GROUP BY results for my Oracle DB query?

I've just started working in an Oracle database after years of using MySQL and I'm a little confused about the grouping differences. As a starting point, let's say I have this project table:

id name created
1 Created on September 2nd 02-09-2023 00:00:00:000
2 Created on September 3rd 03-09-2023 00:00:00:000
3 Created on October 2nd 02-10-2023 00:00:00:000
4 Created on October 3rd 03-10-2023 00:00:00:000
5 Created on October 4th 04-10-2023 00:00:00:000
6 Created on November 1st 01-11-2023 01:00:00:000

I'm trying to write a query that would give me the number of projects that were created within every month in a provided date range (e.g. 01-08-2023 00:00:00:000 to 04-12-2023 00:00:00:000) in a format that ultimately looks like this:

projectsCreated date
0 Aug 2023
2 Sep 2023
3 Oct 2023
1 Nov 2023
0 Dec 2023

How would I go about accomplishing this? So far, all of my attempts have ended with me running into various GROUP BY-related Oracle errors like "ORA-00979".

If it helps, this is the query I was using to accomplish this within MySQL:

SELECT 
  Count(DISTINCT p.id) AS projectsCreated, 
  Date_format(p.created, '%b %Y') AS date 
FROM 
  project p
WHERE 
  (
    Unix_timestamp(p.created) >= Unix_timestamp('01-08-2023 00:00:00') 
    AND Unix_timestamp(p.created) <= Unix_timestamp('04-12-2023 00:00:00')
  ) 
GROUP BY 
  date 
ORDER BY 
  p.created ASC;

Thank you for any and all help you can offer.

EDIT:

Here's the closest I've gotten so far:

SELECT 
  COUNT(p.id) as "COUNT(p.id)",
  TO_CHAR(p.created, 'Mon YYYY') as "createdDate"
FROM 
  project p
WHERE (
    p.created >= to_timestamp('2023-08-01 00:00:00', 'YYYY-MM-DD HH24:mi:SS')
    AND p.created <= to_timestamp('2023-12-04 00:00:00', 'YYYY-MM-DD HH24:mi:SS')
  )
GROUP BY p.id, TO_CHAR(p.created, 'Mon YYYY');

This gives me a result like this:

enter image description here

I've also tried this query, but it just results in an ORA-00979: not a GROUP BY expression error:

SELECT
    Count(DISTINCT(id)) AS "projectsCreated",
    p.created,
    TO_CHAR(created, 'Mon YYYY') as "createdDate"
FROM (
      SELECT 
        p.*,
        TO_CHAR(p.created, 'Mon YYYY') as "createdDate"
      FROM 
        project p
      WHERE 
        (
          p.created >= to_timestamp('2023-08-01 00:00:00', 'YYYY-MM-DD HH24:mi:SS')
          AND p.created <= to_timestamp('2023-12-04 00:00:00', 'YYYY-MM-DD HH24:mi:SS')
        )
)
GROUP BY 
       TO_CHAR(created, 'Mon YYYY')
      ORDER BY 
        p.created ASC;
Solution 
  • CREATE TABLE project (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(255),
    created TIMESTAMP
);


    INSERT INTO project (id, name, created) VALUES
(1, 'Created on September 2nd', TO_TIMESTAMP('02-09-2023 00:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3')),
(2, 'Created on September 3rd', TO_TIMESTAMP('03-09-2023 00:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3')),
(3, 'Created on October 2nd', TO_TIMESTAMP('02-10-2023 00:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3')),
(4, 'Created on October 3rd', TO_TIMESTAMP('03-10-2023 00:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3')),
(5, 'Created on October 4th', TO_TIMESTAMP('04-10-2023 00:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3')),
(6, 'Created on November 1st', TO_TIMESTAMP('01-11-2023 01:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3'));

    Query:

    SELECT 
  Count(DISTINCT p.id) AS projectsCreated, 
  TO_CHAR(p.created, 'Mon YYYY') AS dates 
FROM 
  project p 
WHERE   
    created BETWEEN TO_TIMESTAMP('01-08-2023 00:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3')
    AND TO_TIMESTAMP('04-12-2023 00:00:00:000', 'DD-MM-YYYY HH24:MI:SS:FF3')
GROUP BY 
  TO_CHAR(p.created, 'Mon YYYY')
ORDER BY 
  TO_CHAR(p.created, 'Mon YYYY') ASC;
    PROJECTSCREATED DATES
    1 Nov 2023
    3 Oct 2023
    2 Sep 2023

    fiddle

    If you need to show 0 value for the months with no projects you can use below query:

    with projects as
  (SELECT 
  Count(DISTINCT p.id) AS projectsCreated, 
  TO_CHAR(p.created, 'Mon YYYY') AS dates 
FROM 
  project p 
WHERE   
    created BETWEEN TO_TIMESTAMP('01-08-2023', 'DD-MM-YYYY')
    AND TO_TIMESTAMP('04-12-2023', 'DD-MM-YYYY')
GROUP BY 
  TO_CHAR(p.created, 'Mon YYYY')
ORDER BY 
  TO_CHAR(p.created, 'Mon YYYY') ASC
), ms as
  (
  SELECT ADD_MONTHS(TRUNC(TO_DATE('01-08-2023', 'DD-MM-YYYY'), 'MONTH'), LEVEL - 1) AS DATES
FROM dual
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('01-08-2023', 'DD-MM-YYYY'), 'MONTH'), LEVEL - 1) <= TO_DATE('04-12-2023', 'DD-MM-YYYY')

  )
  
select COALESCE(projectscreated,0) projectscreated,ms.dates from ms left join projects p on TO_CHAR(ms.dates,'Mon YYYY')=p.dates
ORDER BY dates

    Output:

    PROJECTSCREATED DATES
    0 01-AUG-23
    2 01-SEP-23
    3 01-OCT-23
    1 01-NOV-23
    0 01-DEC-23

    fiddle