Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT COUNT(customers.hash) FROM customers
- LEFT JOIN persons ON persons.agent_code
- WHERE customers.agent_code = persons.agent_code
- GROUP BY customers.agent_code
- CREATE TABLE IF NOT EXISTS `customers` (
- `hash` varchar(32) NOT NULL,
- `date_joined` date NOT NULL,
- `agent_code` int(5) NOT NULL DEFAULT '0',
- PRIMARY KEY (`hash`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- INSERT INTO `customers` (`hash`, `date_joined`, `agent_code`) VALUES
- ('0323619e9dd37726ad9aede6b8941022', '2012-09-17', 20004),
- ('0a5a74acc39773c191b87b759799b0c0', '2012-08-02', 22109),
- ('1aa4d97ba79dce047d519efe3832b5e5', '2012-07-19', 22109),
- ('2605578b2e35f01f473591d8f3ed3c51', '2012-08-06', 20003),
- ('26ce0904a6ea30da9b181a301937664e', '2012-07-30', 20003);
- CREATE TABLE IF NOT EXISTS `persons` (
- `agent_code` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`agent_code`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- INSERT INTO `persons` (`agent_code`) VALUES
- (20003),(20004);
- CREATE TABLE IF NOT EXISTS `customers` (
- `hash` varchar(32) NOT NULL,
- `date_joined` date NOT NULL,
- `agent_code` int(5) NOT NULL DEFAULT '0',
- PRIMARY KEY (`hash`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- INSERT INTO `customers` (`hash`, `date_joined`, `agent_code`) VALUES
- ('0323619e9dd37726ad9aede6b8941022', '2012-09-17', 20004),
- ('0a5a74acc39773c191b87b759799b0c0', '2012-08-02', 22109),
- ('1aa4d97ba79dce047d519efe3832b5e5', '2012-07-19', 22109),
- ('2605578b2e35f01f473591d8f3ed3c51', '2012-08-06', 20003),
- ('26ce0904a6ea30da9b181a301937664e', '2012-07-30', 20003);
- CREATE TABLE IF NOT EXISTS `persons` (
- `agent_code` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`agent_code`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- INSERT INTO `persons` (`agent_code`) VALUES
- (20003),(20004);
- SELECT DATE_FORMAT(customers.date_joined, '%W %e') AS 'dt', persons.agent_code AS 'agent', COUNT(*) AS 'customers'
- FROM customers
- INNER JOIN persons ON persons.agent_code = customers.agent_code
- WHERE persons.agent_code = 20003
- GROUP BY customers.date_joined, persons.agent_code
- SELECT DATE_FORMAT(customers.date_joined, '%W %e') AS 'dt', persons.agent_code AS 'agent', COUNT(*) AS 'customers'
- FROM customers
- INNER JOIN persons ON persons.agent_code = customers.agent_code
- WHERE WEEK(customers.date_joined) = WEEK(NOW())
- GROUP BY customers.date_joined, persons.agent_code
- select c.agent_code, c.date_joined, count(c.hash)
- from
- customers c
- left join
- persons p on p.agent_code = c.agent_code
- where weekofyear(date_joined) = weekofyear(current_date)
- group by c.agent_code, c.date_joined
- ;
- +------------+-------------+---------------+
- | agent_code | date_joined | count(c.hash) |
- +------------+-------------+---------------+
- | 20004 | 2012-09-17 | 1 |
- +------------+-------------+---------------+
Add Comment
Please, Sign In to add comment