SQL - count rows in join for each day in a time range-CodePudding

I would like to count for each day valid items in a table. For example:

I have in my table table1 5 entries with following myDate values:

 1. 01.10.2022 09:13
 2. 01.10.2022 11:33
 3. 01.10.2022 11:40
 5. 02.10.2022 07:00
 6. 04.10.2022 06:30

Now I would like to count for each day the number of rows, with the following result:

 1. 01.10.2022 - 3 rows 
 2. 02.10.2022 - 1 row 
 3. 04.10.2022 - 1 row

I've already found a statement to iterate through each day of a period and join my table. But the problem is, that I am receiving duplicate values, if there are multiple values valid in my join condition:

 1. 01.10.2022 - 1 row 
 2. 01.10.2022 - 1 row 
 3. 01.10.2022 - 1 row 
 4. 02.10.2022 - 1 row 
 5.  ....

here is my statement:

SELECT    all_date, COALESCE (cnt, 0)
FROM      (SELECT to_date('01/10/2022', 'dd/mm/yyyy')   rownum - 1 AS all_date
           FROM   dual
           CONNECT BY LEVEL <= 30) d
LEFT JOIN (SELECT   TRUNC(myDate) as myDate, COUNT(myDate) AS cnt
           FROM     table1 
           GROUP BY myDate) r ON d.all_date < TRUNC(r.myDate);

Thanks in advance!

CodePudding user response：

The exact query depends on your setup, but basically, you need something like

SELECT TRUNC(yourdate) AS yourdate, COUNT(*) AS rowCount FROM yourtable 
GROUP BY TRUNC(yourdate) ORDER BY TRUNC(yourdate);

CodePudding user response：

You should GROUP BY expression TRUNC(myDate), not just myDate. Also the join condition should be d.all_date = r.myDate since the value in r is already truncated and you want one row per day in calendar.

Db fiddle (I improved naming - all_date feels better name for relation rather than column)