I have the attached table structure showing only related fields to this questions. For those columns without a heading, I just inserted the client name to reference in this question
I need to show the number of clients served by a given user by service.
For example, user Nataliia had 3 clients (Alissia, Peter and Rose). Under the progress_details you will see that Rose needs help with Family Matters and Senior Support.
Therefore, Nataliia is serving clients that need
1. Senior Support
1. Family Matters
This is part 1.
Part 2 is that If possible, I also need to account for intake.services. So, also under Nataliia:
Alissia needs 12 (Debt Management) and 10 (Employment)
Peter needs Housing
and Rose needs Family Matters and Senior Support.
So, from part 1 plus part 2, Nataliia's final report should be.
1. Senior Support *
1. Family Matters *
1. Debt Management
*Since Senior Support and Family Matters are for Rose, both in Intake and in Progress Details they are for the same client and therefore counted once, but if Peter also require Family Matters then for Nataliia Family Matters would have been 2.
If part 2 is too complex, then I could run a separate query and calculate it with php. But, I don't know how to approach part 1 as well. I would definitely need distinct client_id.