SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    ::==:: Bonzo_CS's Avatar
    Join Date
    Dec 2003
    Location
    (Cardiff Wales)
    Posts
    747
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    calculating repeat customers

    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

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    not quite as ugly but not a whole lot better --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    ::==:: Bonzo_CS's Avatar
    Join Date
    Dec 2003
    Location
    (Cardiff Wales)
    Posts
    747
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey thanks for the quick reply. The solutions worked great! Cheers

  5. #5
    ::==:: Bonzo_CS's Avatar
    Join Date
    Dec 2003
    Location
    (Cardiff Wales)
    Posts
    747
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Bonzo_CS
    Does this query contain a function not in 3.23 ?
    yes, the subqueries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    ::==:: Bonzo_CS's Avatar
    Join Date
    Dec 2003
    Location
    (Cardiff Wales)
    Posts
    747
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Upgrading is definitely a good idea. 3.23 is going to be completely outdated in the very near future.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •