CODEWITHSUNDEEP
mysqlaggregate-functions
ILikeTacos
ILikeTacos

Reputation: 18686

how to selectively count records and group them by date in mysql?

I have three MySQL Tables: customers, persons, and dates_d

customers and persons are related through agent_code

dates_d is just a table with all the dates of the last and following 10 years.

that key allows me to determine how many customers each person has entered. I also have another key with the date a customer was entered, therefore I can query the database and see how many customers have been entered each Month, Year, Week, etc...

My current sample of the database contains records from last August, if I run the following query:

 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

it will tell me the amount of customers each agent has. but I need to know how many customers, every agent entered the current week, and grouped by day.

I know is not that hard, and I more or less thought of a way to make it work:

  1. join dates_d and customers table
  2. join customers table and persons table.
  3. Filter the information by date and group them by person, by day.

So I can display it as:

Thomas, Bryan:

Monday 17: 20 customers
Tuesday 18: 12 customers.
Wednesday 19: 3 customers.
Thursday 20: 0 customers

and so on.

yet again, I've been trying different queries, but I haven't been able to come up with the info I need out of the database. I'm not asking for the specific query, but any help that points me to the right direction will be greatly appreciated!

Thanks!

EDIT 1: Sample Data:

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);

Upvotes: 2

Views: 165

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

For the current week:

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 |
+------------+-------------+---------------+

If you want another week then replace current_date for any date within that week.

Upvotes: 0

Kermit
Kermit

Reputation: 34063

Is this what you are looking for? The query below shows result for agent_code 20003. You can add criteria based on date (such as a date range or specify the week) as needed.

You can see it in action here.

Query

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

Result

|        DT | AGENT | CUSTOMERS |
---------------------------------
| Monday 30 | 20003 |         1 |
|  Monday 6 | 20003 |         1 |

Upvotes: 1

Related Questions