SQL Query to bring up Counts for Users in the European Union

[calvis]

We are using Ablecommerce Gold. We are looking for a SQL query to show us the count of registered users from the European Union.

I can do it a query for each country, but I can't figure how to do it for a group on countries. I need the number of users separated by country.

This information would be helpful in determining if it's feasible to setup a EU distribution point.

[jmestep]

What about setting up a zone and using a join to that? Just a zone for convenience, not for shipping. Or a string of EU country abbreviations and use an in('FR',DE')

[david-ebt]

If you're working directly in SQL, you can this query:

Code: Select all

select c.Name, COUNT(*) as NumUsers
from ac_Users u
join ac_Addresses a on u.UserId = a.UserId and a.IsBilling = 1
join ac_Countries c on a.CountryCode = c.CountryCode
where u.IsApproved = 1 and u.IsAnonymous = 0
group by c.Name

[calvis]

I am using Web2Market's Report Wizard so I can do SQL inside of Ablecommerce.

That was a very nice query. It gave me info that I was looking for plus more.

Thanks,

-Charles