Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    474
    Plugin Contributions
    2

    Default Help with a SQL Query for Query Builder

    I'm trying to create a SQL query to add to the Query Builder table and need some assistance. Been running in circles for awhile

    I'm looking for a query very close to "Customer has placed <X orders and has not placed any orders in Y Amount of time".

    The second part I've been attempting to utilize from the existing queries. Specifically:
    Code:
    select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC
    It is the "<X Orders" part I can't get. My attempts to integrate the COUNT function have failed.

    And if someone manages that, maybe the following add on: "Customer has placed <X orders and has ordered product X and has not placed any orders in Y Amount of time".

  2. #2
    Join Date
    Nov 2005
    Location
    los angeles
    Posts
    2,668
    Plugin Contributions
    11

    Default Re: Help with a SQL Query for Query Builder

    oh... i love an sql challenge!

    you need to do a left join back onto the orders table. i get a little confused as to whether its an inner join or an outer join. but you can use this as your starting point:

    Code:
    select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname, ct.ord_count from TABLE_CUSTOMERS c
    join TABLE_ORDERS o on c.customers_id = o.customers_id
    left join (select count(orders_id) as ord_count, customers_email_address from TABLE_ORDERS group by customers_email_address) ct on c.customers_email_address = ct.customers_email_address
    WHERE c.customers_newsletter = 1 and ct.ord_count < 25
    GROUP BY c.customers_email_address HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH)
    ORDER BY c.customers_lastname, c.customers_firstname ASC
    once you see what is going on there, i think the rest of your questions will answer themselves..... i hope!

    good luck!
    author of square Webpay.
    mxWorks has premium plugins. donations: venmo or paypal accepted.
    premium consistent excellent support. available for hire.

  3. #3
    Join Date
    Jun 2007
    Posts
    474
    Plugin Contributions
    2

    Default Re: Help with a SQL Query for Query Builder

    "Samsonite! I was way off!" - Yeah even my use off Count was nowhere close. Thanks!

    Is vinsrare.com a site you consult for or are you NJ based? (I'm in NJ as well)

 

 

Similar Threads

  1. Replies: 9
    Last Post: 12 Jul 2023, 12:26 AM
  2. v151 SQL query setup. How do I TEST a query 'off-line'?
    By lewisasding in forum General Questions
    Replies: 3
    Last Post: 8 Mar 2013, 12:24 AM
  3. Help with a sql query
    By batteryman in forum General Questions
    Replies: 21
    Last Post: 3 Oct 2008, 11:12 PM
  4. Help with SQL query
    By Danielle in forum General Questions
    Replies: 3
    Last Post: 9 Jun 2008, 05:24 AM
  5. Help with SQL query returning multiple rows
    By acetate in forum General Questions
    Replies: 3
    Last Post: 9 Mar 2008, 03:23 AM

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