Counting query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Counting query

Author

Topic

rlull
Starting Member

39 Posts

Posted - 2005-11-11 : 13:04:34

I have a Customers table and an Order table. I want to count how many "first orders" are placed each day. This means that the customer only has a single order registered to their customer ID. 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'

This only gives me the numbers of orders today - but I need to find out how many of these orders are "first orders". Thanks for any help.

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 14:02:28

Hi rlull, Welcome to SQL Team!


SELECT COUNT(*)
FROM
(
    SELECT CustID, MIN(OrderDateTime)
    FROM Orders
    GROUP BY CustID
    HAVING     MIN(OrderDateTime) >= DATEADD(Day, DATEDIFF(Day, 0, MIN(OrderDateTime)), 0)
           AND MIN(OrderDateTime) < DATEADD(Day, DATEDIFF(Day, 0, MIN(OrderDateTime))+1, 0)
) X

Kristen

rlull
Starting Member

39 Posts

Posted - 2005-11-11 : 14:24:17

Thank you, Kristen. I appreciate your help. At the risk of sounding silly, what is X? When I run the query I get a message that says "No column was specified for column 2 of 'X'."

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 14:27:17

"X" is an "alias name" for the sub query.

2nd attempt!


SELECT COUNT(*)
FROM
(
    SELECT CustID, MIN(OrderDateTime) AS MIN_OrderDateTime
    FROM Orders
    GROUP BY CustID
    HAVING     MIN(OrderDateTime) >= DATEADD(Day, DATEDIFF(Day, 0, MIN(OrderDateTime)), 0)
           AND MIN(OrderDateTime) < DATEADD(Day, DATEDIFF(Day, 0, MIN(OrderDateTime))+1, 0)
) AS X

Kristen

rlull
Starting Member

39 Posts

Posted - 2005-11-11 : 15:26:33

Thanks, again!

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources