Yoni Green Yoni Green - 4 years ago 147
MySQL Question

SQL - Selecting a customer that only bought 1 type of product on a specific date

I need to find out how many pencils were bought on 2017-01-01 by people that only bought 1 other type of product prior to buying pencils. (e.g. bought only notebooks beforehand)
This is what I have, that so far shows many who bought one type of product beforehand, so what I am missing is how many pencils they bought on the 2017-01-01:

SELECT
c.name,
s.units_sold AS Sold,
s.product_id

FROM
sales AS s
INNER JOIN customers AS c
ON c.id=s.customer_id
GROUP BY c.name
HAVING COUNT(DISTINCT s.product_id) = 1


I tried to look at similar questions without success.
Hope my question is clear :/

Thanks!

Answer Source

This seems like a very strange question. But if I read literally, then you would seem to want something like this:

select sum(s.units_sold)
from sales s
where s.product_id = 'pencil' and
      s.date = '2017-01-01' and
      1 = (select count(distinct s2.product_id)
           from sales s2 
           where s2.customer_id = s.customer_id and 
                 s2.date < s.date
          );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download