Greetings all,

 

I have a question that I suspect an SQL Guru will be able to answer in 5 seconds...
Here goes...

 

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...

 

Any ideas?

in MultiStore by (1.1k points)
edited by

2 Answers

+2 votes
 
Best answer

Perhpas you can do min(o.ordernumber) for each customer?

by (870 points)
selected by
+1 vote
Try this code:

SELECT Month(O.shippedon) AS Month,
       Year(O.shippedon) AS Year,
       Count(DISTINCT O.ordernumber) AS OrderQty,
       Count(DISTINCT OS.shoppingcartrecid) AS Lines,
(select count(DISTINCT CustomerID) from orders O2 where Month(O2.shippedon)=Month(O.shippedon) and Year(O2.shippedon)=Year(O.shippedon)) as customer_count
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)

Edit: Bah code won't format and I had it set for order date for testing.
by (5k points)
Thanks Mate - this is what I ended up with :-

SELECT Month(O.shippedon)            AS Month,
       Year(O.shippedon)             AS Year,
       Count(DISTINCT O.ordernumber) AS OrderQty,
       Count(oi.ordernumber)         AS NewOrders
FROM   orders O
       LEFT JOIN (SELECT Min(ordernumber) AS OrderNumber
                  FROM   orders
                  WHERE  shippedon IS NOT NULL
                  GROUP  BY customerid) oi
              ON O.ordernumber = oi.ordernumber
WHERE  Year(O.shippedon) >= 2013 AND O.shippedon IS NOT NULL
GROUP  BY Year(O.shippedon), Month(O.shippedon)
ORDER  BY Year(O.shippedon), Month(O.shippedon)


Grrr - hate this comment formatting!
...