title: Mysql Querying A Subset of Data
body: <p>I have 2 queries, the first query is to list all customer who made purchases from the very beginning of time:</p>
  
  <pre><code>SELECT o.id_customer, CONCAT(c.firstname, ' ', c.lastname) AS fullname, c.email, COUNT(o.id_customer) AS 'total'
  FROM ps_orders AS o
  LEFT JOIN ps_order_state_lang AS osl ON o.current_state = osl.id_order_state
  LEFT JOIN ps_customer AS c ON o.id_customer = c.id_customer
  WHERE o.current_state IN(2,4,5)
  AND osl.id_lang = 2
  AND o.invoice_date BETWEEN '2014-01-01' AND '2015-09-30'
  GROUP BY o.id_customer
  ORDER BY total DESC;
  </code></pre>
  
  <p>While the second query is to list all customer who made a purchase on a specific period of time:</p>
  
  <pre><code>SELECT c.id_customer, CONCAT(c.firstname, ' ', c.lastname) AS fullname, COUNT(c.id_customer) AS total
  FROM ps_orders AS o
  LEFT JOIN ps_customer AS c ON o.id_customer = c.id_customer
  WHERE o.invoice_date BETWEEN '2015-01-01' AND '2015-01-31'
  AND o.current_state IN (2,4,5)
  GROUP BY c.id_customer
  ORDER BY total DESC;
  </code></pre>
  
  <p>Based on this, how can i get all customers who only made their first purchase based on the period specified on the second query. How can i do this using these 2 queries ? Thanks.</p>
  
  <p>EDIT #1</p>
  
  <p>My current solution is this:</p>
  
  <pre><code>SELECT a.id_customer, a.fullname, a.total AS 'this_month', b.total AS 'all_time'
  FROM (
      SELECT c.id_customer, CONCAT(c.firstname, ' ', c.lastname) AS fullname, COUNT(c.id_customer) AS total
      FROM ps_orders AS o
      LEFT JOIN ps_customer AS c ON o.id_customer = c.id_customer
      WHERE o.invoice_date BETWEEN '2015-01-01' AND '2015-01-31'
      AND o.current_state IN (2,4,5)
      GROUP BY c.id_customer
      HAVING COUNT(c.id_customer) &lt; 2
      ORDER BY total DESC
  ) AS a
  LEFT JOIN (
      SELECT o.id_customer, CONCAT(c.firstname, ' ', c.lastname) AS fullname, c.email, COUNT(o.id_customer) AS 'total'
      FROM ps_orders AS o
      LEFT JOIN ps_order_state_lang AS osl ON o.current_state = osl.id_order_state
      LEFT JOIN ps_customer AS c ON o.id_customer = c.id_customer
      WHERE o.current_state IN(2,4,5)
      AND osl.id_lang = 2
      AND o.invoice_date BETWEEN '2013-12-31' AND '2015-01-01'
      GROUP BY o.id_customer
      HAVING COUNT(o.id_customer) &lt; 2
      ORDER BY total DESC
  ) AS b ON b.id_customer = a.id_customer
  ORDER BY all_time DESC;
  </code></pre>
  
  <p>is there a better way of doing this ?</p>
  
  <p>EDIT 2:</p>
  
  <p>First query is listing of all customers who made purchases at our store from the beginning of time.</p>
  
  <p>Second query is listing of all customer who only made purchases in a specific month.</p>
  
  <p>Based on this 2 queries, i would like to list all customer who made purchases in a specific month for the first time.</p>
  
  <p>EDIT #3:
  Perhaps i wasn't very clear with what i want to achieve, so let me elaborate more.</p>
  
  <p>I have a table named ps_orders, which stores all the transaction record from the very beginning. From ps_orders i particularly interested in the following column:</p>
  
  <ul>
  <li>id_customer</li>
  <li>id_order</li>
  <li>invoice_date</li>
  </ul>
  
  <p>I want to know how many first time buyers from a given period of time. For example:</p>
  
  <p>I want to know how many customer is a first time buyer between 01 january 2015 and 31 january 2015. The following query lists all orders that is made between that exact date:</p>
  
  <pre><code>SELECT c.id_customer, CONCAT(c.firstname, ' ', c.lastname) AS fullname, COUNT(c.id_customer) AS total
  FROM ps_orders AS o
  LEFT JOIN ps_customer AS c ON o.id_customer = c.id_customer
  WHERE o.invoice_date BETWEEN '2015-01-01' AND '2015-01-31'
  AND o.current_state IN (2,4,5)
  GROUP BY c.id_customer
  ORDER BY total DESC;
  </code></pre>
  
  <p>How ever the result includes the customer who has made purchases in earlier date (e.g. Cust ID 1234 may have purchased in the previous year). However i am only interested only the first time buyer within that period of time. Please see EDIT #1 to see my current solution (it contains some invalid data, but i think i am getting close to what i want)</p>