Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Need help with a query please

    I am trying to write a query to establish the number of repeat customers (customers with more than 1 order over 3 years) compared to the total orders.

    I believe I have the correct queries, however I am looking for someone to confirm this as I could be wrong and I need to be 100% sure. I'd be very grateful for any help to validate my queries.

    Please see below:

    Code:
    //get timestamps for query
    $howlongago = '-36 months';
    $from = strtotime($howlongago);
    $to = time();
    
    //get total orders in period
    $totalorders =  mysql_num_rows(mysql_query(("
    SELECT 
     (order_id)
    FROM orders
    WHERE status='C' AND orders.timestamp BETWEEN '$from' AND '$to'
    GROUP BY email
    ")));
    
    //get total unique customers with more than one order
    $repeats = mysql_num_rows(mysql_query(("
    SELECT ( order_id )
    FROM orders
    WHERE status='C' AND orders.timestamp BETWEEN '$from' AND '$to'
    GROUP BY email
    HAVING (COUNT( email ) >1)
    ")));
    
    
    
    //display this as a percentage
    $percy = round($repeats / $totalorders * 100,2);
    
    echo "Repeat customer %: $percy%";
    Is my method correct?

    Many thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    It should work, but it's really really ugly coding.

    It requires that the DB server return *ALL THE ROWS* to PHP just so PHP can count them! Talk about overkill on data transfer.

    Do the counting in MySQL, so you don't have to transfer all the data to PHP!

    No, I take that back! Your first query is wrong. Because you do the GROUP BY, you will *NOT* be getting *ALL* orders. You will only be getting the number of *customers*.

    Let's kill all that and start over. And let's keep PHP out of the mix.
    Code:
    SELECT COUNT(*) AS totalOrders
    FROM orders
    WHERE status='C' 
    AND orders.timestamp BETWEEN '$from' AND '$to'
    short and sweet and simple.

    Code:
    SELECT COUNT(X.*) AS totalRepeatCustomers
    FROM ( SELECT email, COUNT(*) as ordersPerEmail
           FROM orders
           WHERE status='C' 
           AND orders.timestamp BETWEEN '$from' AND '$to'
           GROUP BY email
           HAVING COUNT(*) > 1 ) AS X
    Now execute those queries, read the one and only value returned by MySQL to PHP, and do your division.

    We could even do the division in SQL code, so you'd only need one query. But it's probably not worth the effort, since we'd have to do essentially the same as both the above and wrap it all up in a big ugly query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    shoryuken (05-10-2012)

  • #3
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you very much indeed. That is a much more efficient way of doing it and I am glad the output works out the same (aside from my group by error which you kindly pointed out).


  •  

    Posting Permissions

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