How to Count the Number of Orders Each Day with SQL

View All Scripts Login to Run Script

Script Name How to Count the Number of Orders Each Day with SQL
Description An example of using group by to get daily order totals.
Area SQL General
Contributor Chris Saxon (Oracle)
Created Friday November 01, 2019

Statement 1

select order_id, order_datetime,  
       customer_id, store_id 
from   co.orders 
where  order_datetime >= date'2019-04-01'


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 2

Passing order_datetime to to_char converts the timestamp to a string, according to the format mask. This allows you to "remove" the time from the date.

Formattting the date

select order_datetime, to_char ( order_datetime, 'dd Mon yyyy' )   
from   co.orders   
where  order_datetime >= date'2019-04-01'


ORDER_DATETIME TO_CHAR(ORDER_DATETIME,'DDMONYYYY')
01-APR-19 05.27.32.922332 AM 01 Apr 2019
01-APR-19 11.16.45.297958 AM 01 Apr 2019
01-APR-19 01.37.14.376157 PM 01 Apr 2019
01-APR-19 02.03.52.732709 PM 01 Apr 2019
01-APR-19 03.19.17.592317 PM 01 Apr 2019
02-APR-19 01.48.41.157805 AM 02 Apr 2019
02-APR-19 03.52.49.213390 PM 02 Apr 2019
03-APR-19 10.47.49.417719 AM 03 Apr 2019
03-APR-19 01.40.01.840676 PM 03 Apr 2019
03-APR-19 02.02.33.418296 PM 03 Apr 2019
03-APR-19 07.05.04.600442 PM 03 Apr 2019
04-APR-19 12.53.58.792754 AM 04 Apr 2019
04-APR-19 03.24.06.034344 PM 04 Apr 2019
04-APR-19 08.14.44.084942 PM 04 Apr 2019
04-APR-19 08.24.44.505645 PM 04 Apr 2019
05-APR-19 12.26.49.495590 AM 05 Apr 2019
05-APR-19 08.43.51.766135 AM 05 Apr 2019
05-APR-19 08.57.16.206628 PM 05 Apr 2019
06-APR-19 12.19.35.005649 AM 06 Apr 2019
06-APR-19 04.30.16.153051 AM 06 Apr 2019
06-APR-19 03.32.41.785093 PM 06 Apr 2019
07-APR-19 06.54.49.039864 AM 07 Apr 2019
07-APR-19 06.31.50.146426 PM 07 Apr 2019
08-APR-19 06.54.19.862383 AM 08 Apr 2019
08-APR-19 08.57.49.237653 AM 08 Apr 2019
08-APR-19 11.16.55.199255 PM 08 Apr 2019
10-APR-19 03.36.17.661842 AM 10 Apr 2019
10-APR-19 08.14.01.961369 AM 10 Apr 2019
10-APR-19 10.14.27.406048 AM 10 Apr 2019
10-APR-19 03.49.31.059138 PM 10 Apr 2019
10-APR-19 06.22.55.181577 PM 10 Apr 2019
11-APR-19 03.49.08.760887 AM 11 Apr 2019
11-APR-19 03.49.45.728115 AM 11 Apr 2019
11-APR-19 07.14.31.588169 PM 11 Apr 2019
12-APR-19 09.41.06.642641 PM 12 Apr 2019

35 rows selected.

ORDER_DATETIME	TO_CHAR(ORDER_DATETIME,'DDMONYYYY')
01-APR-19 05.27.32.922332 AM	01 Apr 2019
01-APR-19 11.16.45.297958 AM	01 Apr 2019
01-APR-19 01.37.14.376157 PM	01 Apr 2019
01-APR-19 02.03.52.732709 PM	01 Apr 2019
01-APR-19 03.19.17.592317 PM	01 Apr 2019
02-APR-19 01.48.41.157805 AM	02 Apr 2019
02-APR-19 03.52.49.213390 PM	02 Apr 2019
03-APR-19 10.47.49.417719 AM	03 Apr 2019
03-APR-19 01.40.01.840676 PM	03 Apr 2019
03-APR-19 02.02.33.418296 PM	03 Apr 2019
03-APR-19 07.05.04.600442 PM	03 Apr 2019
04-APR-19 12.53.58.792754 AM	04 Apr 2019
04-APR-19 03.24.06.034344 PM	04 Apr 2019
04-APR-19 08.14.44.084942 PM	04 Apr 2019
04-APR-19 08.24.44.505645 PM	04 Apr 2019
05-APR-19 12.26.49.495590 AM	05 Apr 2019
05-APR-19 08.43.51.766135 AM	05 Apr 2019
05-APR-19 08.57.16.206628 PM	05 Apr 2019
06-APR-19 12.19.35.005649 AM	06 Apr 2019
06-APR-19 04.30.16.153051 AM	06 Apr 2019
06-APR-19 03.32.41.785093 PM	06 Apr 2019
07-APR-19 06.54.49.039864 AM	07 Apr 2019
07-APR-19 06.31.50.146426 PM	07 Apr 2019
08-APR-19 06.54.19.862383 AM	08 Apr 2019
08-APR-19 08.57.49.237653 AM	08 Apr 2019
08-APR-19 11.16.55.199255 PM	08 Apr 2019
10-APR-19 03.36.17.661842 AM	10 Apr 2019
10-APR-19 08.14.01.961369 AM	10 Apr 2019
10-APR-19 10.14.27.406048 AM	10 Apr 2019
10-APR-19 03.49.31.059138 PM	10 Apr 2019
10-APR-19 06.22.55.181577 PM	10 Apr 2019
11-APR-19 03.49.08.760887 AM	11 Apr 2019
11-APR-19 03.49.45.728115 AM	11 Apr 2019
11-APR-19 07.14.31.588169 PM	11 Apr 2019
12-APR-19 09.41.06.642641 PM	12 Apr 2019

Statement 3

Group by separates the rows into the same unit with the same value for to_char ( order_datetime, 'dd Mon yyyy' ). The query returns one row for each group.

Group the rows

select to_char ( order_datetime, 'dd Mon yyyy' ),  
       count (*)  
from   co.orders  
where  order_datetime >= date'2019-04-01'  
group  by to_char ( order_datetime, 'dd Mon yyyy' )


TO_CHAR(ORDER_DATETIME,'DDMONYYYY') COUNT(*)
12 Apr 2019 1
03 Apr 2019 4
04 Apr 2019 4
10 Apr 2019 5
11 Apr 2019 3
05 Apr 2019 3
02 Apr 2019 2
07 Apr 2019 2
01 Apr 2019 5
06 Apr 2019 3
08 Apr 2019 3

11 rows selected.

Statement 4
The parameter nls_timestamp_format controls the default timestamp format for to_char. This changes it to show just the month and year.
Set the default format mask
```
alter session set nls_timestamp_format = 'Mon YYYY'
```
```
Statement processed.
```
Statement 5
To_char now picks up the default format mask, Mon YYYY. So it returns Apr 2019 for all these dates. Any client can change their default format, so you can't rely on this. Always specify a format mask.
Using to_char with no format mask
```
select to_char ( order_datetime ),  
       count (*)  
from   co.orders  
where  order_datetime >= date'2019-04-01'  
group  by to_char ( order_datetime )
```
```
TO_CHAR(ORDER_DATETIME) COUNT(*)
Apr 2019 35
```

TO_CHAR(ORDER_DATETIME,'DDMONYYYY')	COUNT(*)
12 Apr 2019	1
03 Apr 2019	4
04 Apr 2019	4
10 Apr 2019	5
11 Apr 2019	3
05 Apr 2019	3
02 Apr 2019	2
07 Apr 2019	2
01 Apr 2019	5
06 Apr 2019	3
08 Apr 2019	3

TO_CHAR(ORDER_DATETIME)	COUNT(*)
Apr 2019	35

Additional Information

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