SQL Exercise: Number of orders booked for each day

Last update on March 08 2025 08:18:33 (UTC/GMT +8 hours)

Daily Order Count Format

From the following table, write a SQL query to find the number of orders booked for each day. Return the result in a format like "For 2001-10-10 there are 15 orders".

Sample table: orders

ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05  3005         5002
70009       270.65      2012-09-10  3001         5005
70002       65.26       2012-10-05  3002         5001
70004       110.5       2012-08-17  3009         5003
70007       948.5       2012-09-10  3005         5002
70005       2400.6      2012-07-27  3007         5001
70008       5760        2012-09-10  3002         5001
70010       1983.43     2012-10-10  3004         5006
70003       2480.4      2012-10-10  3009         5003
70012       250.45      2012-06-27  3008         5002
70011       75.29       2012-08-17  3003         5007
70013       3045.6      2012-04-25  3002         5001

Sample Solution:

-- This query counts the number of orders for each 'ord_date' and provides a descriptive output.
-- It selects constant strings along with calculated counts for each 'ord_date'.
SELECT ' For', ord_date, ',there are', COUNT(ord_no), 'orders.'
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Groups the result set by the 'ord_date' column.
GROUP BY ord_date;

Output of the Query:

?column?	ord_date	?column?	count	?column?
For		2012-04-25	,there are	1	orders.
For		2012-06-27	,there are	1	orders.
For		2012-07-27	,there are	1	orders.
For		2012-08-17	,there are	2	orders.
For		2012-09-10	,there are	3	orders.
For		2012-10-05	,there are	2	orders.
For		2012-10-10	,there are	2	orders.

Code Explanation:

The query in SQL retrieves data from the 'orders' table and returns the information: The string "For", the column ord_date, the string ",there are", the count of the column ord_no, calculated using the COUNT function and grouped by ord_date, and the string "orders."
In the end, the final result that displays the message "For (order_date), there are (count of order_no) orders." for each unique order_date value.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation :

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

For more Practice: Solve these Related Problems:

• Write a SQL query to group orders by ord_date and display the count in the format "On [ord_date]: [count] orders", including only dates with more than one order.
• Write a SQL query to list the order count for each day as "Date: [ord_date] - Total Orders: [count]" while filtering out weekends.
• Write a SQL query to count orders per day and output a formatted string "There were [count] orders on [ord_date]" for days where the count is less than five.
• Write a SQL query to group orders by ord_date and display the result as "For [ord_date], there are [count] orders." sorted in ascending order.
• Write a SQL query to display daily order counts, including only those dates where the order count is a prime number.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Salesmen commission with the percent sign (%).
Next SQL Exercise: Display orders after sorting orders by order number.