How to Count the Number of Orders EVERY Day with SQL

View All Scripts Login to Run Script

Script Name How to Count the Number of Orders EVERY Day with SQL
Description An overview of how to get the total number of orders placed each day, including days with no orders.
Area SQL General
Contributor Chris Saxon (Oracle)
Created Friday November 01, 2019

Statement 1

This generates all the dates in April 2019

Get the days in April

with calendar_dates as (  
  select date'2019-03-31' + level calendar_date   
  from   dual   
  connect by level <= 30  
)  
select *  
from   calendar_dates


CALENDAR_DATE
01-APR-19
02-APR-19
03-APR-19
04-APR-19
05-APR-19
06-APR-19
07-APR-19
08-APR-19
09-APR-19
10-APR-19
11-APR-19
12-APR-19
13-APR-19
14-APR-19
15-APR-19
16-APR-19
17-APR-19
18-APR-19
19-APR-19
20-APR-19
21-APR-19
22-APR-19
23-APR-19
24-APR-19
25-APR-19
26-APR-19
27-APR-19
28-APR-19
29-APR-19
30-APR-19

30 rows selected.

CALENDAR_DATE
01-APR-19
02-APR-19
03-APR-19
04-APR-19
05-APR-19
06-APR-19
07-APR-19
08-APR-19
09-APR-19
10-APR-19
11-APR-19
12-APR-19
13-APR-19
14-APR-19
15-APR-19
16-APR-19
17-APR-19
18-APR-19
19-APR-19
20-APR-19
21-APR-19
22-APR-19
23-APR-19
24-APR-19
25-APR-19
26-APR-19
27-APR-19
28-APR-19
29-APR-19
30-APR-19

Statement 2

Find the orders placed in April

with ords as (  
  select order_id, order_datetime,   
         customer_id, store_id  
  from   co.orders  
  where  order_datetime >= date'2019-04-01'  
)  
select *  
from   ords


ORDER_ID ORDER_DATETIME CUSTOMER_ID STORE_ID
1916 01-APR-19 05.27.32.922332 AM 87 23
1917 01-APR-19 11.16.45.297958 AM 225 22
1918 01-APR-19 01.37.14.376157 PM 254 22
1919 01-APR-19 02.03.52.732709 PM 145 23
1920 01-APR-19 03.19.17.592317 PM 145 23
1921 02-APR-19 01.48.41.157805 AM 261 23
1922 02-APR-19 03.52.49.213390 PM 339 20
1923 03-APR-19 10.47.49.417719 AM 354 1
1924 03-APR-19 01.40.01.840676 PM 232 23
1925 03-APR-19 02.02.33.418296 PM 87 23
1926 03-APR-19 07.05.04.600442 PM 366 18
1927 04-APR-19 12.53.58.792754 AM 196 22
1928 04-APR-19 03.24.06.034344 PM 367 19
1929 04-APR-19 08.14.44.084942 PM 165 20
1930 04-APR-19 08.24.44.505645 PM 338 19
1931 05-APR-19 12.26.49.495590 AM 136 20
1932 05-APR-19 08.43.51.766135 AM 319 23
1933 05-APR-19 08.57.16.206628 PM 254 22
1934 06-APR-19 12.19.35.005649 AM 370 22
1935 06-APR-19 04.30.16.153051 AM 370 22
1936 06-APR-19 03.32.41.785093 PM 312 22
1937 07-APR-19 06.54.49.039864 AM 312 22
1938 07-APR-19 06.31.50.146426 PM 370 22
1939 08-APR-19 06.54.19.862383 AM 174 23
1940 08-APR-19 08.57.49.237653 AM 203 23
1941 08-APR-19 11.16.55.199255 PM 290 23
1942 10-APR-19 03.36.17.661842 AM 203 23
1943 10-APR-19 08.14.01.961369 AM 377 23
1944 10-APR-19 10.14.27.406048 AM 367 19
1945 10-APR-19 03.49.31.059138 PM 341 22
1946 10-APR-19 06.22.55.181577 PM 348 23
1947 11-APR-19 03.49.08.760887 AM 377 23
1948 11-APR-19 03.49.45.728115 AM 319 23
1949 11-APR-19 07.14.31.588169 PM 348 23
1950 12-APR-19 09.41.06.642641 PM 348 23

35 rows selected.

ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	STORE_ID
1916	01-APR-19 05.27.32.922332 AM	87	23
1917	01-APR-19 11.16.45.297958 AM	225	22
1918	01-APR-19 01.37.14.376157 PM	254	22
1919	01-APR-19 02.03.52.732709 PM	145	23
1920	01-APR-19 03.19.17.592317 PM	145	23
1921	02-APR-19 01.48.41.157805 AM	261	23
1922	02-APR-19 03.52.49.213390 PM	339	20
1923	03-APR-19 10.47.49.417719 AM	354	1
1924	03-APR-19 01.40.01.840676 PM	232	23
1925	03-APR-19 02.02.33.418296 PM	87	23
1926	03-APR-19 07.05.04.600442 PM	366	18
1927	04-APR-19 12.53.58.792754 AM	196	22
1928	04-APR-19 03.24.06.034344 PM	367	19
1929	04-APR-19 08.14.44.084942 PM	165	20
1930	04-APR-19 08.24.44.505645 PM	338	19
1931	05-APR-19 12.26.49.495590 AM	136	20
1932	05-APR-19 08.43.51.766135 AM	319	23
1933	05-APR-19 08.57.16.206628 PM	254	22
1934	06-APR-19 12.19.35.005649 AM	370	22
1935	06-APR-19 04.30.16.153051 AM	370	22
1936	06-APR-19 03.32.41.785093 PM	312	22
1937	07-APR-19 06.54.49.039864 AM	312	22
1938	07-APR-19 06.31.50.146426 PM	370	22
1939	08-APR-19 06.54.19.862383 AM	174	23
1940	08-APR-19 08.57.49.237653 AM	203	23
1941	08-APR-19 11.16.55.199255 PM	290	23
1942	10-APR-19 03.36.17.661842 AM	203	23
1943	10-APR-19 08.14.01.961369 AM	377	23
1944	10-APR-19 10.14.27.406048 AM	367	19
1945	10-APR-19 03.49.31.059138 PM	341	22
1946	10-APR-19 06.22.55.181577 PM	348	23
1947	11-APR-19 03.49.08.760887 AM	377	23
1948	11-APR-19 03.49.45.728115 AM	319	23
1949	11-APR-19 07.14.31.588169 PM	348	23
1950	12-APR-19 09.41.06.642641 PM	348	23

Statement 3

This uses a left (outer) join. So it will preserve all the rows from the table on the left of the join (calendar_dates). Any dates with no orders will appear in the results with a null value for order_datetime.

Outer join the orders to the dates

with calendar_dates as (  
  select date'2019-03-31' + level calendar_date   
  from   dual   
  connect by level <= 30  
), ords as (  
  select order_id, order_datetime,   
         customer_id, store_id  
  from   co.orders  
  where  order_datetime >= date'2019-04-01'  
)  
select calendar_date, order_datetime  
from   calendar_dates  
left join ords  
on     order_datetime >= calendar_date  
and    order_datetime < calendar_date + 1


CALENDAR_DATE ORDER_DATETIME
01-APR-19 01-APR-19 05.27.32.922332 AM
01-APR-19 01-APR-19 11.16.45.297958 AM
01-APR-19 01-APR-19 01.37.14.376157 PM
01-APR-19 01-APR-19 02.03.52.732709 PM
01-APR-19 01-APR-19 03.19.17.592317 PM
02-APR-19 02-APR-19 01.48.41.157805 AM
02-APR-19 02-APR-19 03.52.49.213390 PM
03-APR-19 03-APR-19 10.47.49.417719 AM
03-APR-19 03-APR-19 01.40.01.840676 PM
03-APR-19 03-APR-19 02.02.33.418296 PM
03-APR-19 03-APR-19 07.05.04.600442 PM
04-APR-19 04-APR-19 12.53.58.792754 AM
04-APR-19 04-APR-19 03.24.06.034344 PM
04-APR-19 04-APR-19 08.14.44.084942 PM
04-APR-19 04-APR-19 08.24.44.505645 PM
05-APR-19 05-APR-19 12.26.49.495590 AM
05-APR-19 05-APR-19 08.43.51.766135 AM
05-APR-19 05-APR-19 08.57.16.206628 PM
06-APR-19 06-APR-19 12.19.35.005649 AM
06-APR-19 06-APR-19 04.30.16.153051 AM
06-APR-19 06-APR-19 03.32.41.785093 PM
07-APR-19 07-APR-19 06.54.49.039864 AM
07-APR-19 07-APR-19 06.31.50.146426 PM
08-APR-19 08-APR-19 06.54.19.862383 AM
08-APR-19 08-APR-19 08.57.49.237653 AM
08-APR-19 08-APR-19 11.16.55.199255 PM
09-APR-19  - 
10-APR-19 10-APR-19 03.36.17.661842 AM
10-APR-19 10-APR-19 08.14.01.961369 AM
10-APR-19 10-APR-19 10.14.27.406048 AM
10-APR-19 10-APR-19 03.49.31.059138 PM
10-APR-19 10-APR-19 06.22.55.181577 PM
11-APR-19 11-APR-19 03.49.08.760887 AM
11-APR-19 11-APR-19 03.49.45.728115 AM
11-APR-19 11-APR-19 07.14.31.588169 PM
12-APR-19 12-APR-19 09.41.06.642641 PM
13-APR-19  - 
14-APR-19  - 
15-APR-19  - 
16-APR-19  - 
17-APR-19  - 
18-APR-19  - 
19-APR-19  - 
20-APR-19  - 
21-APR-19  - 
22-APR-19  - 
23-APR-19  - 
24-APR-19  - 
25-APR-19  - 
26-APR-19  - 

Rows 1 - 50. More rows exist.

CALENDAR_DATE	ORDER_DATETIME
01-APR-19	01-APR-19 05.27.32.922332 AM
01-APR-19	01-APR-19 11.16.45.297958 AM
01-APR-19	01-APR-19 01.37.14.376157 PM
01-APR-19	01-APR-19 02.03.52.732709 PM
01-APR-19	01-APR-19 03.19.17.592317 PM
02-APR-19	02-APR-19 01.48.41.157805 AM
02-APR-19	02-APR-19 03.52.49.213390 PM
03-APR-19	03-APR-19 10.47.49.417719 AM
03-APR-19	03-APR-19 01.40.01.840676 PM
03-APR-19	03-APR-19 02.02.33.418296 PM
03-APR-19	03-APR-19 07.05.04.600442 PM
04-APR-19	04-APR-19 12.53.58.792754 AM
04-APR-19	04-APR-19 03.24.06.034344 PM
04-APR-19	04-APR-19 08.14.44.084942 PM
04-APR-19	04-APR-19 08.24.44.505645 PM
05-APR-19	05-APR-19 12.26.49.495590 AM
05-APR-19	05-APR-19 08.43.51.766135 AM
05-APR-19	05-APR-19 08.57.16.206628 PM
06-APR-19	06-APR-19 12.19.35.005649 AM
06-APR-19	06-APR-19 04.30.16.153051 AM
06-APR-19	06-APR-19 03.32.41.785093 PM
07-APR-19	07-APR-19 06.54.49.039864 AM
07-APR-19	07-APR-19 06.31.50.146426 PM
08-APR-19	08-APR-19 06.54.19.862383 AM
08-APR-19	08-APR-19 08.57.49.237653 AM
08-APR-19	08-APR-19 11.16.55.199255 PM
09-APR-19	-
10-APR-19	10-APR-19 03.36.17.661842 AM
10-APR-19	10-APR-19 08.14.01.961369 AM
10-APR-19	10-APR-19 10.14.27.406048 AM
10-APR-19	10-APR-19 03.49.31.059138 PM
10-APR-19	10-APR-19 06.22.55.181577 PM
11-APR-19	11-APR-19 03.49.08.760887 AM
11-APR-19	11-APR-19 03.49.45.728115 AM
11-APR-19	11-APR-19 07.14.31.588169 PM
12-APR-19	12-APR-19 09.41.06.642641 PM
13-APR-19	-
14-APR-19	-
15-APR-19	-
16-APR-19	-
17-APR-19	-
18-APR-19	-
19-APR-19	-
20-APR-19	-
21-APR-19	-
22-APR-19	-
23-APR-19	-
24-APR-19	-
25-APR-19	-
26-APR-19	-

Statement 4

To get the number of orders/day, you need to count a column from the orders table. This gives the number of non-null rows in the group. Count(*) returns the number of rows in the group. So this will incorrectly return 1 for days with zero orders.

Group by and count

with calendar_dates as (  
  select date'2019-03-31' + level calendar_date   
  from   dual   
  connect by level <= 30  
), ords as (  
  select order_id, order_datetime,   
         customer_id, store_id  
  from   co.orders  
  where  order_datetime >= date'2019-04-01'  
)  
select calendar_date,  
       count ( order_datetime ) order#,   
       count (*) ct  
from   calendar_dates  
left join ords  
on     order_datetime >= calendar_date  
and    order_datetime < calendar_date + 1  
group  by calendar_date


CALENDAR_DATE ORDER# CT
05-APR-19 3 3
25-APR-19 0 1
27-APR-19 0 1
07-APR-19 2 2
08-APR-19 3 3
19-APR-19 0 1
12-APR-19 1 1
21-APR-19 0 1
23-APR-19 0 1
01-APR-19 5 5
04-APR-19 4 4
06-APR-19 3 3
10-APR-19 5 5
14-APR-19 0 1
18-APR-19 0 1
20-APR-19 0 1
28-APR-19 0 1
03-APR-19 4 4
09-APR-19 0 1
16-APR-19 0 1
17-APR-19 0 1
26-APR-19 0 1
02-APR-19 2 2
11-APR-19 3 3
13-APR-19 0 1
15-APR-19 0 1
22-APR-19 0 1
24-APR-19 0 1
29-APR-19 0 1
30-APR-19 0 1

30 rows selected.

CALENDAR_DATE	ORDER#	CT
05-APR-19	3	3
25-APR-19	0	1
27-APR-19	0	1
07-APR-19	2	2
08-APR-19	3	3
19-APR-19	0	1
12-APR-19	1	1
21-APR-19	0	1
23-APR-19	0	1
01-APR-19	5	5
04-APR-19	4	4
06-APR-19	3	3
10-APR-19	5	5
14-APR-19	0	1
18-APR-19	0	1
20-APR-19	0	1
28-APR-19	0	1
03-APR-19	4	4
09-APR-19	0	1
16-APR-19	0	1
17-APR-19	0	1
26-APR-19	0	1
02-APR-19	2	2
11-APR-19	3	3
13-APR-19	0	1
15-APR-19	0	1
22-APR-19	0	1
24-APR-19	0	1
29-APR-19	0	1
30-APR-19	0	1

Statement 5

Group by DOES NOT SORT! So to get the results in date order, add an order by.

Sort the results

with calendar_dates as (  
  select date'2019-03-31' + level calendar_date   
  from   dual   
  connect by level <= 30  
), ords as (  
  select order_id, order_datetime,   
         customer_id, store_id  
  from   co.orders  
  where  order_datetime >= date'2019-04-01'  
)  
select calendar_date,  
       count ( order_datetime ) order#  
from   calendar_dates  
left join ords  
on     order_datetime >= calendar_date  
and    order_datetime < calendar_date + 1  
group  by calendar_date  
order  by calendar_date


CALENDAR_DATE ORDER#
01-APR-19 5
02-APR-19 2
03-APR-19 4
04-APR-19 4
05-APR-19 3
06-APR-19 3
07-APR-19 2
08-APR-19 3
09-APR-19 0
10-APR-19 5
11-APR-19 3
12-APR-19 1
13-APR-19 0
14-APR-19 0
15-APR-19 0
16-APR-19 0
17-APR-19 0
18-APR-19 0
19-APR-19 0
20-APR-19 0
21-APR-19 0
22-APR-19 0
23-APR-19 0
24-APR-19 0
25-APR-19 0
26-APR-19 0
27-APR-19 0
28-APR-19 0
29-APR-19 0
30-APR-19 0

30 rows selected.

CALENDAR_DATE	ORDER#
01-APR-19	5
02-APR-19	2
03-APR-19	4
04-APR-19	4
05-APR-19	3
06-APR-19	3
07-APR-19	2
08-APR-19	3
09-APR-19	0
10-APR-19	5
11-APR-19	3
12-APR-19	1
13-APR-19	0
14-APR-19	0
15-APR-19	0
16-APR-19	0
17-APR-19	0
18-APR-19	0
19-APR-19	0
20-APR-19	0
21-APR-19	0
22-APR-19	0
23-APR-19	0
24-APR-19	0
25-APR-19	0
26-APR-19	0
27-APR-19	0
28-APR-19	0
29-APR-19	0
30-APR-19	0

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database