How to Display Total number of Orders on a Certain Day in MySQL

Question

Hello

I would like to know how to display orders placed on a certain day.

For example:

I would like to display orders placed today.

My MySQL database contains the following tables:

1) orders
2) orders_statuses

Under orders, I have the following fields:
1) orders_id
2) orders_placed_date

Under orders_statuses, I have the following fields:
1) status_id
2) status_name

Whenever a new order is placed, it gets the default status_id = 3 (meaning status_name= Order is Pending)

So, if today 3 orders were placed, all of those will have the same status_id, which is 3. (Order is Pending)

Now, what query should I use to calculate orders placed today, and this week and this month?

Here is the query where I select all the orders placed in my cart:

SELECT   order_id, order_placed_date FROM  orders

The Result of the above query:

Order ID ---- Order Date Placed
1 --- 12/30/2008 12:06:24 AM
2 --- 2/3/2009 1:57:17 AM
3 --- 2/3/2009 1:58:27 AM
4 --- 5/3/2009 1:58:48 AM
5 --- 6/3/2009 2:00:31 AM
6 --- 7/3/2009 2:01:47 AM
7 --- 7/3/2009 2:02:31 AM
9 --- 7/4/2009 2:21:18 PM
10 --- 7/4/2009 2:21:36 PM
11 --- 7/4/2009 2:22:18 PM
12 --- 7/4/2009 2:23:29 PM
13 --- 7/4/2009 2:24:24 PM

What I would like is to get the total number of orders placed today.

Since today's date is 7 July 2009, you can see from the above result that there are 5 orders placed on 7th July.

What query should I use to get the total number of orders in a certain day, in today's case, 5.

UPDATE : Solved

I used both Adam's and colithium querys to get the answer:

Here is what I used:

---

SELECT   COUNT(order_placed_date)
FROM     va_orders
WHERE    DATE(order_placed_date) = curdate();

---

It worked great :)

Answer 1

To get the number of orders on a certain day:

SELECT COUNT(*) FROM orders WHERE order_placed_date = @DayYouWant

To get the orders from today/this week/this month:

SELECT order_id, order_placed_date FROM orders WHERE order_placed_date BETWEEN @StartDate AND @EndDate

If you want only orders with a status of 3 you will need to JOIN with the status table and add that criteria to the WHERE clause.

Answer 2

Assuming you have a foreign key in ORDERS_STATUSES called ORDERS_ID:

select o.orders_id
       , o.orders_placed_date
from   orders o
       inner join orders_statuses os
       os.orders_id = o.orders_id
    and
       os.status_id = 3
where  date(o.orders_placed_date) = curdate()