MySql LEFT JOIN with COUNT

Question

I have two tables, customers and sales. I want to count sales for each customer and create a table of sales per month for each store.

I would like to produce something like;

------------------------------
month  |  customers  | sales  |
------------------------------
1/2013 |      5      |   2    |
2/2013 |      21     |   9    |
3/2013 |      14     |   4    |
4/2013 |      9      |   3    |

but I am having trouble getting the sales count to be correct when using the following;

SELECT CONCAT(MONTH(c.added), '/', YEAR(c.added)), count(c.id), count(s.id)
FROM customers c
LEFT JOIN sales s 
ON s.customer_id = c.id AND MONTH(c.added) = MONTH(s.added) AND YEAR(c.added) = YEAR(s.added)
WHERE c.store_id = 1
GROUP BY YEAR(c.added), MONTH(c.added);

Customers table;

-------------------------------
id    |   store_id  | added    |
-------------------------------
1     |      1      |2013-02-01 |
2     |      1      |2013-02-02 |
3     |      1      |2013-03-16 |

sales table;

---------------------------------
id    |   added    | customer_id |
---------------------------------
1     | 2013-02-18 |     3       |
2     | 2013-03-02 |     2       |
3     | 2013-03-16 |     3       |

Can anyone help here?

thanks

Hi @hims056, i have a customers table; [id, added] and a sales table [id, added, customer_id]. The customers table records when the customer was added to the system, and the sales table records when a customer makes a purchase — Christy Herron, Jun 04 '13 at 12:02
Yes I can see that in your question but show the schema and some sample data. (same as you show your expected output) — Himanshu, Jun 04 '13 at 12:03
@hims056 I have added the tables, and some data. Does that help? — Christy Herron, Jun 04 '13 at 12:11
@ChristyHerron Go to http://www.sqlfiddle.com/ and put a CREATE TABLE code with some sample data. Then your SELECT, that doesn't work. — user4035, Jun 04 '13 at 12:14
@ChristyHerron Why this condition: "AND MONTH(c.added) = MONTH(s.added) AND YEAR(c.added) = YEAR(s.added)"? — user4035, Jun 04 '13 at 12:16
It looks like your query works just fine: http://sqlfiddle.com/#!2/e2dcf/1 — Olexa, Jun 04 '13 at 12:18
Hi @Olexa, as Mark points out below, my query only counts sales made in the same month as the customer was added — Christy Herron, Jun 04 '13 at 12:40

score 1 · Accepted Answer · 2013-06-04T13:02:14.200

1

(Updated) The existing query will only count sales made in the same month that the customer was added. Try this, instead:

SELECT CONCAT(MONTH(sq.added), '/', YEAR(sq.added)) month_year,
       sum(sq.customer_count), 
       sum(sq.sales_count)
FROM (select s.added, 0 customer_count, 1 sales_count
      from customers c
      JOIN sales s ON s.customer_id = c.id
      WHERE c.store_id = 1
      union all
      select added, 1 customer_count, 0 sales_count
      from customers
      WHERE store_id = 1) sq
GROUP BY YEAR(sq.added), MONTH(sq.added);

edited Jun 04 '13 at 13:02

answered Jun 04 '13 at 12:24

thanks @Mark, thats great. You seem to have found the issue with my own query. Unfortunately I cant guarantee that a sale will be made in the same month as the customer was added. Is it possible for you to create the 'more complicated' query? Thank you – Christy Herron Jun 04 '13 at 12:30
@ChristyHerron: This should return new customers per month, and sales per month, for the specified store - note that the sales_count is grouped on the sales table added date. What leads you to think it is returning too many results? Have you tried checking the results for a specific month against the actual records in the database? – Jun 04 '13 at 15:38
Thanks @Mark, my mistake, your query works great. Thank you so much, im very grateful. – Christy Herron Jun 04 '13 at 17:25

score 0 · Answer 2 · edited Feb 24 '14 at 21:44

0

SELECT c.* , s.sales_count<br>
FROM customers c<br>
LEFT JOIN (SELECT customer_id, count(id) as sales_count FROM sales GROUP BY customer_id) s on c.id=s.customer_id<br>
WHERE c.store_id = 1<br>

edited Feb 24 '14 at 21:44

VladL

12,769
10
63
83

answered Feb 24 '14 at 21:24

user3348544

1

Kindly include in your answer as well a brief explanation of why this works or what doesn't work in the OP's code such that this answer remedies the problem. :) – WGS Feb 24 '14 at 21:49

MySql LEFT JOIN with COUNT

2 Answers2

Linked