I have a question that I suspect an SQL Guru will be able to answer in 5 seconds...
SELECT Month(O.shippedon) AS Month,
Year(O.shippedon) AS Year,
Count(DISTINCT O.ordernumber) AS OrderQty,
Count(DISTINCT OS.shoppingcartrecid) AS Lines
FROM orders O JOIN orders_shoppingcart OS ON O.ordernumber = OS.ordernumber
WHERE Year(O.shippedon) >= 2013
GROUP BY Year(O.shippedon), Month(O.shippedon)
ORDER BY Year(O.shippedon), Month(O.shippedon)
That query gives me number of orders and number of order lines per month.
I'd like to add another column that tells me how many of the orders per month where the first orders for a customer.
I thought about joining a subquery that lists CustomerIDs that only have one entry in the order table - but this won't work as it's based on the number of orders now - rather than then...