Retrieve new customers per month 1

[serializer]

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!

 

[imex]

Try:

select 
    Month(o.TradeDate) as TradeMonth,
    Year(o.TradeDate) as TradeYear,
    Count(*) as QtyNewCustomer
from Order as o
where not exists (select 1 from Orders as s 
                  where s.CustomerId = o.CustomerId and
                        s.OrderId < o.OrderId)

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[serializer]

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

 

[imex]

Try to replace OrderId by the Primary Key of the table [Order].

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[imex]

And the Group By like this:

group by
    Month(o.TradeDate),
    Year(o.TradeDate)

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[serializer]

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!