Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Retrieve new customers per month 1

Status
Not open for further replies.

serializer

Programmer
May 15, 2006
143
SE
I have an order table with customer id and tradedate. I would like to get statistics about number of new customers for each month. I defined a new customer by an order row with a customerid I have not counted before. I am not sure if this is possible through SQL only.

Here is my sample query:

select customerid,tradedate from [order] where paid = 1

Basically I would like a result like this:

201301 5
201302 4
201303 6
etc

Thank you!
 
Thanks, but unfortunately that did not work. I got different errors. Missing group by. Also, the table name is [order] and not [orders]. The column orderid does not exist. I tried to fix to the following but no rows where returned:

select
Month(o.TradeDate) as TradeMonth,
Year(o.TradeDate) as TradeYear,
Count(*) as QtyNewCustomer
from [Order] as o
where not exists (select 1 from [Order] as s
where s.CustomerId = o.CustomerId )
group by TradeDate
 
Thanks, that worked!

Here is the query I ended up with using:

select
Month(o.TradeDate) as TradeMonth,
Year(o.TradeDate) as TradeYear,
Count(*) as QtyNewCustomer
from [Order] as o
where paid = 1 and not exists (select 1 from [Order] as s
where s.CustomerId = o.CustomerId and
s.Id < o.Id)
group by
Month(o.TradeDate),
Year(o.TradeDate)
order by Year(o.TradeDate) desc,Month(o.TradeDate) desc


Thanks again!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top