wYWmXrmT

1SELECT COUNT(customers.hash) FROM customers
2 LEFT JOIN persons ON persons.agent_code
3 WHERE customers.agent_code = persons.agent_code
4 GROUP BY customers.agent_code
5	
6CREATE TABLE IF NOT EXISTS `customers` (
7  `hash` varchar(32) NOT NULL,
8  `date_joined` date NOT NULL,
9  `agent_code` int(5) NOT NULL DEFAULT '0',
10  PRIMARY KEY (`hash`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8;
12
13INSERT INTO `customers` (`hash`, `date_joined`, `agent_code`) VALUES
14('0323619e9dd37726ad9aede6b8941022', '2012-09-17', 20004),
15('0a5a74acc39773c191b87b759799b0c0', '2012-08-02', 22109),
16('1aa4d97ba79dce047d519efe3832b5e5', '2012-07-19', 22109),
17('2605578b2e35f01f473591d8f3ed3c51', '2012-08-06', 20003),
18('26ce0904a6ea30da9b181a301937664e', '2012-07-30', 20003);
19
20
21
22
23 CREATE TABLE IF NOT EXISTS `persons` (
24  `agent_code` int(11) NOT NULL DEFAULT '0',
25  PRIMARY KEY (`agent_code`)
26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
27
28
29
30  INSERT INTO `persons` (`agent_code`) VALUES
31  (20003),(20004);
32	
33CREATE TABLE IF NOT EXISTS `customers` (
34  `hash` varchar(32) NOT NULL,
35  `date_joined` date NOT NULL,
36  `agent_code` int(5) NOT NULL DEFAULT '0',
37  PRIMARY KEY (`hash`)
38) ENGINE=InnoDB DEFAULT CHARSET=utf8;
39
40INSERT INTO `customers` (`hash`, `date_joined`, `agent_code`) VALUES
41('0323619e9dd37726ad9aede6b8941022', '2012-09-17', 20004),
42('0a5a74acc39773c191b87b759799b0c0', '2012-08-02', 22109),
43('1aa4d97ba79dce047d519efe3832b5e5', '2012-07-19', 22109),
44('2605578b2e35f01f473591d8f3ed3c51', '2012-08-06', 20003),
45('26ce0904a6ea30da9b181a301937664e', '2012-07-30', 20003);
46
47
48
49
50 CREATE TABLE IF NOT EXISTS `persons` (
51  `agent_code` int(11) NOT NULL DEFAULT '0',
52  PRIMARY KEY (`agent_code`)
53 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
54
55
56
57  INSERT INTO `persons` (`agent_code`) VALUES
58  (20003),(20004);
59	
60SELECT DATE_FORMAT(customers.date_joined, '%W %e') AS 'dt', persons.agent_code AS 'agent', COUNT(*) AS 'customers'
61FROM customers 
62INNER JOIN persons ON persons.agent_code = customers.agent_code
63WHERE persons.agent_code = 20003
64GROUP BY customers.date_joined, persons.agent_code
65	
66SELECT DATE_FORMAT(customers.date_joined, '%W %e') AS 'dt', persons.agent_code AS 'agent', COUNT(*) AS 'customers'
67FROM customers 
68INNER JOIN persons ON persons.agent_code = customers.agent_code
69WHERE WEEK(customers.date_joined) = WEEK(NOW())
70GROUP BY customers.date_joined, persons.agent_code
71	
72select c.agent_code, c.date_joined, count(c.hash) 
73from 
74    customers c
75    left join 
76    persons p on p.agent_code = c.agent_code
77where weekofyear(date_joined) = weekofyear(current_date) 
78group by c.agent_code, c.date_joined
79;
80+------------+-------------+---------------+
81| agent_code | date_joined | count(c.hash) |
82+------------+-------------+---------------+
83|      20004 | 2012-09-17  |             1 |
84+------------+-------------+---------------+