user3514092 user3514092 - 9 days ago 8
MySQL Question

MYSQL: two joins on same table become double grouped count. How to fix it?

Having this simplified structure:

customers
-----------------------
id
name
salesagents_id (each client has their own sales agent assigned)

salesagents
-----------------------
id
name


visits
-----------------------
customers_id
salesagents_id
date


I need to get a list of all the clients, plus three additional fields:


  1. Name of sales agent assigned to client

  2. Number of visits received by salesagents

  3. Number of visits received in last month



This is the query I have so far:

SELECT clients.*, salesagents.name, COUNT(v1.id) as visits_number, COUNT(v2.id) as visits_number_last_month
FROM `clients`
LEFT JOIN `salesagents` ON `clients`.`salesagents_id`=`salesagents`.`id`
LEFT JOIN `visits` as `v1` ON `clients`.`id` = `v1`.`clients_id`
LEFT JOIN `visits` as `v2` ON `clients`.`id` = `v2`.`clients_id` AND `v2`.`date` > FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH)))
GROUP BY `clients`.`id`


The problem is that the two joins on the same table makes up double number of visits.

What else could I do?

Answer

Distinct Count may solve your problem

SELECT clients.*, salesagents.name, COUNT(DISTINCT v1.id) as visits_number, COUNT( DISTINCT v2.id) as visits_number_last_month
FROM `clients`
LEFT JOIN `salesagents` ON `clients`.`salesagents_id`=`salesagents`.`id`
LEFT JOIN `visits` as `v1` ON `clients`.`id` = `v1`.`clients_id`
LEFT JOIN `visits` as `v2` ON `clients`.`id` = `v2`.`clients_id` AND `v2`.`date` > FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH)))
GROUP BY `clients`.`id`