CODEWITHSUNDEEP
sql
goldenone
goldenone

Reputation: 11

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

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!

edit: I used d.all_date < trunc(r.myDate) because I would like to select valid values in a timerange.

myKey myDatf myDatt
1 01.10.2022 08:00 14.10.2022 08:30
2 01.10.2022 09:00 07.10.2022 09:30
3 03.10.2022 11:00 03.10.2022 12:00
4 07.10.2022 08:00 08.10.2022 11:00

Based on previous table, I would like to get the following result. At 04.10.2022 00:00:00 there were 2 valid values with datf < 04.10.2022 00:00:00 and datt > 04.10.2022 00:00:00.

date count
02.10.2022 00:00:00 1
03.10.2022 00:00:00 1
04.10.2022 00:00:00 2
05.10.2022 00:00:00 2
06.10.2022 00:00:00 2
07.10.2022 00:00:00 3
08.10.2022 00:00:00 2
... ...

Upvotes: 0

Views: 659

Answers (2)

Tom&#225;š Z&#225;lusk&#253;
Tom&#225;š Z&#225;lusk&#253;

Reputation: 12000

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)

UPDATE: You changed the specification of problem. You need to first join on expression testing range and then group by. Note your example gives wrong result 3 for 07.10. Also note the condition with between and and behaves differently (and IMO correct) for edge case where time is exactly on midnight. Finally note the count(myKey) - we cannot use count(*) because it would count rows with null right side of left join as 1.

with table1 (myKey,myDatf,myDatt) as (
  select  1, to_date('01.10.2022 08:00','dd.MM.yyyy HH:MI'), to_date('14.10.2022 08:30','dd.MM.yyyy HH:MI') from dual union all
  select  2, to_date('01.10.2022 09:00','dd.MM.yyyy HH:MI'), to_date('07.10.2022 09:30','dd.MM.yyyy HH:MI') from dual union all
  select  3, to_date('03.10.2022 11:00','dd.MM.yyyy HH:MI'), to_date('03.10.2022 12:00','dd.MM.yyyy HH:MI') from dual union all
  select  4, to_date('07.10.2022 08:00','dd.MM.yyyy HH:MI'), to_date('08.10.2022 11:00','dd.MM.yyyy HH:MI') from dual
)
select all_date.calendar_day, count(table1.myKey)
from      (SELECT to_date('01/10/2022', 'dd/mm/yyyy') + rownum - 1 AS calendar_day
           FROM   dual
           CONNECT BY LEVEL <= 30
) all_date
left join table1 on all_date.calendar_day between table1.myDatf and table1.myDatt
group by all_date.calendar_day
order by all_date.calendar_day;

Db fiddle

Upvotes: 1

Jonas Metzler
Jonas Metzler

Reputation: 5975

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);

Upvotes: 1

Related Questions