Finding first order in a single year-CodePudding

I'm trying to determine how many new people made an order in 2018. This looks straight forward enough but there is an error with putting calculated fields in the WHERE statement.

SELECT DISTINCT COUNT(c.customer_id)
FROM Customer c
LEFT JOIN
    Orders o ON c.customer_id=o.customer_id
WHERE MIN(order_date) > '2017-12-31'
AND MIN(order_date) < '2019-01-01';

CodePudding user response：

You can achieve this by putting a sequence number to the orders and then selecting the first row for each customer. Although, I'm not really sure why you're performing a count of the orders when you just want to consider the first orders. Nevertheless the below should work just fine.

SELECT count(res.customer_id) FROM (
   SELECT c.customer_id,
   ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY o.order_date ASC) row_num 
   FROM Customer c
   LEFT JOIN Orders o ON c.customer_id=o.customer_id
   WHERE o.order_date > '2017-12-31'
   AND o.order_date < '2019-01-01'
) res WHERE res.row_num=1

CodePudding user response：

Join with a subquery that finds the customers that were new in 2018.

SELECT COUNT(DISTINCT o.customer_id)
FROM Orders o
JOIN (
    SELECT DISTINCT customer_id
    FROM Orders
    GROUP BY customer_id
    HAVING MIN(order_date) > '2017-12-31'
) o1 ON o1.customer_id = o.customer_id
WHERE o.order_date < '2019-01-01';

There's also no need to join with Customers, since the customer ID is in Orders.

And the correct way to get the distinct count is COUNT(DISTINCT o.customer_id), not DISTINCT COUNT(o.customer_id).