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

    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,079
    Plugin Contributions
    7

    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!
    help with WCAG is now here! PM if you want some help with this. (or any ZC issue).
    if you feel so inclined, feel free to send some cake....

  3. #3
    Join Date
    Jun 2007
    Posts
    470
    Plugin Contributions
    1

    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: 4
    Last Post: 17 Oct 2013, 10:55 PM
  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