oracle query to split data into per-date rows

Question

I need to create a report which splits the values from table A into a row-by-date format. So for example If I create a sub-set of dates for the past week and sales in the past week, I need to output the 'REPORT OUTPUT' format. Just struggling to think of a way to 'loop' through the dates in date_range and say find sales in sales_in_range and do a count, then output the results.

WITH
 date_range (date_val)
 as
 (select sysdate-7 + level 
 from dual 
 connect by level <= ( sysdate - (sysdate-7))
 ),
 sales_in_range (itemcode,sale_date,value)
 as
 (select itemcode,sale_date,value 
 from sale_table 
 where sale_date between (sysdate-7) and (sysdate)
 );
-- REPORT OUTPUT SQL
;

Sub queries provide the below information.

Date Range:

select * from date_range
date_val
12-NOV-21
13-NOV-21
14-NOV-21
15-NOV-21
16-NOV-21
17-NOV-21
18-NOV-21

Sales in date range:

select * from sales_in_range;
itemcode sale_date value
00001 12-NOV-21 0
00002 13-NOV-21 0
00003 15-NOV-21 0
00004 15-NOV-21 0
00005 16-NOV-21 0
00006 16-NOV-21 0
00007 16-NOV-21 0
00008 18-NOV-21 0
00009 18-NOV-21 0
00010 18-NOV-21 0

REPORT OUTPUT:

sale_date sale_count
12-NOV-21 1
13-NOV-21 1
14-NOV-21 0
15-NOV-21 2
16-NOV-21 3
17-NOV-21 0
18-NOV-21 3

Thanks in advance

K

Answer 1

I'm not really sure what the purpose of the query you've written so far is supposed to be, but any reason you can't just simply use the GROUP BY clause with the COUNT() aggregate function on your sale_table like so?

SELECT sale_date, COUNT(*) AS sale_count
FROM sale_table
GROUP BY sale_date
ORDER BY sale_date

If you need to filter on just a certain date range, then you can add a WHERE clause also (assuming sale_date is of a date-based data type):

SELECT sale_date, COUNT(*) AS sale_count
FROM sale_table
WHERE sale_date >= TO_DATE('SomeStartDate', 'SomeDateFormat')
 AND sale_date < TO_DATE('SomeEndDate', 'SomeDateFormat')
GROUP BY sale_date
ORDER BY sale_date