Wrox Programmer Forums
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 11th, 2005, 12:27 PM
Registered User
Join Date: Sep 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Counting unique orders

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'
Old November 12th, 2005, 12:34 AM
Friend of Wrox
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Orders Page with GridView chenimen BOOK: Wrox's ASP.NET 2.0 Visual Web Developer 2005 Express Edition Starter ISBN: 978-0-7645-8807-5 1 March 3rd, 2006 07:36 AM
counting unique items malekmac Access 15 November 29th, 2005 04:20 PM
counting unique entries Figgis Access 5 November 23rd, 2005 07:01 PM
Counting unique record dlamarche Access 5 March 22nd, 2005 08:51 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.