calculating repeat customers

[Bonzo_CS]

Hi All,

I have tried several methods to solve the following but still no joy......


Basically what I am trying to do is calculate the percentage of customers who return and purchase (repeat custom).

I have a customer Table with a unique Customer_ID and a seperate order table which has the RID of Customer_ID. What I am trying to do is calculate how many customers have made more than one purchase and compare this to the number of customers to get a percentage figure?

Any ideas how I can run a query on the order table to find out where a customer_ID occurs more than once?

Cheers

[longneck]

select count(counter) as repeat_customers
     , ( select count(*)
           from customer ) as customer_count
     , count(counter) /
       ( select count(*)
           from customer ) as repeat_percentage
  from ( select 1 as counter
           from order
         group
             by customer_id
         having count(*) > 1 ) as dt

ugly, but i can't think of anything better right now.

[r937]

not quite as ugly but not a whole lot better --

select repeat_customers
     , customer_count
     , repeat_customers /
       customer_count   as repeat_percentage
  from ( select count(counter) 
             as repeat_customers
           from ( select 1 as counter
                    from order
                  group
                      by customer_id
                  having count(*) > 1 ) as dt  
       ) as dt2  
cross
  join ( select count(*) as customer_count
           from customer ) as dt3

[Bonzo_CS]

Hey thanks for the quick reply. The solutions worked great! Cheers

[Bonzo_CS]

Hi Guys,

I have done all I can not to revisit this post, however something odd has happened?

I used the code you wrote on my local server and it worked great. I have now uploaded the same code to a online server, with a duplicate database table structure, but its not working?

Any reasons why this could happen?

The server has MySQL 3.23.58

The local server has MySQL 4.1.9

Does this query contain a function not in 3.23 ?

Thanks in advance

[r937]

Does this query contain a function not in 3.23 ?

yes, the subqueries

[Bonzo_CS]

Hi r937,

Thanks for getting back to me. As I see this as a potential long term problem, I'll now upgrade to a newer version of MySQL.

Cheers for the feedback

[Hartmann]

Upgrading is definitely a good idea. 3.23 is going to be completely outdated in the very near future.