views:

223

answers:

0

Group, I am looking for a suggestion to speed up my query. I am using a CTE to organize my data. I am pulling customer records based on account numbers, customertype and length they have been a customer. In the second part of the CTE I say COUNT(AcctNumber) AS Total and then I also want to do an additional nested select that will give me the total for the specific customer and customer type... basically a sum. So in my nested select I have (SELECT COUNT(AccountNumber) FROM Groups WHERE CustomerID = a.CustomerID AND CustomerType = a.CustomerType) AS Subtotal.

I am basically doing this so I can take Total divided by Subtotal which will give me the percent. The query without the nested select takes 15 seconds and with the nested select it took over an hour. Not sure if there is a better way to do something like this when using a CTE or if I should do it completely different.

Any help is greatly appreciated!