Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...

AspDotNetStorefront
DotFeed

SQL GROUP BY / HAVING question

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?

asked Oct 9, 2013 in MultiStore by BFG 9000 (1,050 points)
edited Oct 9, 2013 by BFG 9000

2 Answers

+2 votes
 
Best answer

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

answered Oct 9, 2013 by donato (870 points)
selected Oct 9, 2013 by BFG 9000
+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.
answered Oct 9, 2013 by mmcgeachy (5,045 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!
...