I have two tables Meeting and client. I have two foreign keys in the meeting table id_client1 and id_client2. How can I count the number of meetings that took place between clients?
I have such a query
SELECT client.id_client, count(meet.id_client1)count_meeting
FROM meet JOIN client ON meet.id_client1=client.id_client
GROUP BY client.id_client;
But this query only count meeting client1. What can I do to count the meetings for both clients?
I would like such a result
id_client count_meeting
1 3
2 1
3 2
You may want a union of the sets of clients:
SELECT id_client, COUNT(*)
FROM
(
SELECT id_client1 AS id_client FROM meet
UNION ALL
SELECT id_client2 AS id_client FROM meet
) participants
GROUP BY id_client
ORDER BY id_client;
And if you want to include clients that didn't participate in any meeting:
SELECT c.id_client, COUNT(p.id_client)
FROM client c
LEFT JOIN
(
SELECT id_client1 AS id_client FROM meet
UNION ALL
SELECT id_client2 AS id_client FROM meet
) p ON p.id_client = c.id_client
GROUP BY c.id_client
ORDER BY c.id_client;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments