|
Subject:
|
Counting unique orders
|
|
Posted By:
|
rlull
|
Post Date:
|
11/11/2005 11:27:53 AM
|
I have a Customers table and an Order table. I am trying to count the number of "first time orders" each day. Note that customers can exist in the table without first having made an order. So basically, a customer ID should only exist once in the orders table to qualify as a "first time order" but how do I check this? Thanks for any help. Here's what I have so far:
SELECT COUNT(Orders.OrderID) AS NewOrdersToday
FROM Orders
INNER JOIN Customers on Customers.CustID = Orders.CustID
WHERE CAST(CONVERT(char(10), Orders.OrderDateTime, 112) as smalldatetime) = '11/11/2005'
|
|
Reply By:
|
robprell
|
Reply Date:
|
11/11/2005 11:34:56 PM
|
select a count of a distinct value. I have to test the syntax but something like:
select count(distinct orders.customerid) ..... blah blah blah.
You can't do it as easily if you select the order id. If one customer had three orders there would be three rows, sounds like you only want one. Thus hopefully you have a customerID in your order table. If so a simple distinct should do the trick.
|
