COUNT UNIQUE data in multiple joins mysql

April 18, 2023

enter code hereI want make one report from multiple table and count unique record
My database structure is as follows:

Companies:

| id | title |
| --------   | 
| 1  | apple |

Clients:

| id | name   |
| ---|--------|
| 1  | Jordan |

company_clients

| ID | company_id | client_id  |
| -- | ---------- | ---------- |
| 1  |    1       |     1      |

service_clients

| ID | client_id | service_name |
| -- | --------- | -----------  |
| 1  | 1         | x1           |
| 2  | 1         | x2           |
| 3  | 1         | x3           |

service_client_dailies

| ID | service_id | service_date | distance |
| -- | --------- | ------------- | -------- |
| 1  | 1         | 2023-02-03    | 15       |
| 1  | 1         | 2023-02-04    | 0        |
| 1  | 1         | 2023-02-05    | 7        |
| 1  | 2         | 2023-02-05    | 15       |
| 1  | 2         | 2023-02-07    | 6        |

I want write query know each company have how much client with distance > 5

this is my query :

    SELECT
    companies.title,
       COUNT ( company_clients.ID ) 
FROM
    companies
    LEFT JOIN company_clients ON company_clients.company_id = companies.ID 
    INNER JOIN service_clients ON service_clients.client_id = company_clients.client_id 
    INNER JOIN service_client_dailies ON service_client_dailes.service_id = service_clients.id 
    WHERE  service_client_dailies.distance > 5
GROUP BY 
    companies.id,company_clients.client_id

I want to know how many customers for each company has with this condition (distance > 5)
Ex : Apple : 5 customer but my query no have good result

>Solution :

Try this query :

select c.id, c.title, count(cc.id)
from companies c
inner join company_clients cc on c.id = cc.company_id
inner join service_clients sc on sc.client_id = cc.client_id
inner join service_client_dailies scd on scd.service_id = sc.id
where scd.distance > 5
group by c.id, c.title

If you want to count unique clients then change count(cc.id) to count(distinct cc.id)