SQL Exercise: Number of orders booked for each day
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 :

Go to:
PREV : Salespeople with Percent Commission.
NEXT : All Orders Sorted by Order Number.
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.