Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Location
    UK
    Posts
    478
    Plugin Contributions
    0

    Default creating a query for customers that have only ordered once - mySQL

    Hiya

    My brain is struggling with this one - I need a MySQL query that pulls out the details for customers that have only ordered once - and as I would like to use query builder it would be great if it was in 1 query (hence my issue)

    this seems to give me the details or number of orders per customer_id:
    Code:
    SELECT c.customers_id, COUNT(c.customers_id) as ccount FROM customers c, orders o WHERE c.customers_id=o.customers_id GROUP BY (c.customers_id)
    but then I am trying to get it into a list of their details
    Code:
    SELECT c.customers_email_address, c.customers_lastname, c.customers_firstname from customers c 
    
    WHERE c.customers_id IN (SELECT c.customers_id, COUNT(c.customers_id) FROM customers c, orders o WHERE c.customers_id=o.customers_id GROUP BY (c.customers_id)) GROUP BY 
    
    c.customers_email_address ORDER BY c.customers_lastname, c.customers_firstname ASC
    but also need to have the COUNT = 1
    failing miserably!

    I can do it in different steps offline but is it possible to do online via query_builder and online?

    thank you
    Sarah

  2. #2
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    10,719
    Plugin Contributions
    79

    Default Re: creating a query for customers that have only ordered once - mySQL

    The following query (which returns all information for matching customer records) appears to do the trick:
    Code:
    SELECT c.*, oc.num_orders
      FROM customers c
         INNER JOIN 
             (SELECT customers_id, COUNT(customers_id) as num_orders
              FROM orders
            GROUP BY customers_id) AS oc
            ON oc.customers_id = c.customers_id
     WHERE oc.num_orders = 1

  3. #3
    Join Date
    May 2004
    Location
    UK
    Posts
    478
    Plugin Contributions
    0

    Default Re: creating a query for customers that have only ordered once - mySQL

    THANK YOU! Much appreciated I always always forget about INNER JOIN! but been too long since I just did MySQL statement - much appreciated thank you

 

 

Similar Threads

  1. Replies: 0
    Last Post: 14 Nov 2011, 07:49 PM
  2. Replies: 2
    Last Post: 27 Apr 2010, 10:05 AM
  3. Query sql needed to remove customers that aint ordered
    By Xbox Memberships in forum General Questions
    Replies: 0
    Last Post: 2 Apr 2010, 04:18 PM
  4. Replies: 6
    Last Post: 26 Jul 2008, 06:11 AM
  5. Contacting Customers that have not ordered anything
    By Nellie in forum Managing Customers and Orders
    Replies: 0
    Last Post: 20 Jun 2006, 06:08 PM

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
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR