AbleCommerce Shopping Cart Software

Posted: **Mon Jan 30, 2017 1:29 pm**

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.

Posted: **Tue Jan 31, 2017 12:38 am**

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')

Posted: **Thu Feb 02, 2017 3:58 am**

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

Posted: **Thu Feb 02, 2017 8:01 am**

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

AbleCommerce Shopping Cart Software

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

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

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

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

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