SQL count rows of one table in relation to another table

Question

I have 3 different tables :

Client

+----+-----------+----------+ | id | firstName | lastName | +----+-----------+----------+ | 1 | John | Doe | | 2 | Jane | Doe | +----+-----------+----------+

Loan

+----+--------+-----------+----------------+ | id | amount | client_id | institution_id | +----+--------+-----------+----------------+ | 1 | 200 | 2 | 3 | | 2 | 400 | 1 | 1 | +----+--------+-----------+----------------+

Institution

+----+---------------+ | id | name | +----+---------------+ | 1 | Institution A | | 2 | Institution B | | 3 | Institution C | +----+---------------+

I am looking to create a list of the number of loans a client has with each institution (for every row in the institution table). Including when a client has 0 loans with an institution.

Something that looks like :

+-----------+-----------+----------+--------------------------+-----------+ | client_id | firstName | lastName | financialInstitutionName | loanCount | +-----------+-----------+----------+--------------------------+-----------+ | 1 | John | Doe | Institution A | 1 | | 1 | John | Doe | Institution B | 0 | | 1 | John | Doe | Institution C | 0 | | 2 | Jane | Doe | Institution A | 0 | | 2 | Jane | Doe | Institution B | 0 | | 2 | Jane | Doe | Institution C | 1 | +-----------+-----------+----------+--------------------------+-----------+

I have tried all manners of joins, subqueries and where clauses but without success. The concept that I do not grasp is how to get a row per institution, per client (total count institution x client). I would love if that query was possible without subqueries or union joins.

Thank you for your time!

Just a word of advice, you will get a better response from the community here if you post at least one of your joins you have tried. — David Lee
– David Lee, Commented Jun 5, 2017 at 23:08
Updated my answer, please let me know if this works for you as I did not test my code — David Lee
– David Lee, Commented Jun 5, 2017 at 23:34

David Lee · Accepted Answer · 2017-06-05 23:37:03Z

First subquery in the FROM setups that data so each client has a record for each of the institutions. This is then joined to a subquery that counts the number of loans.

SELECT d.client_id, d.firstName, d.lastName, d.name AS financialInstitutionName, CASE WHEN l IS NULL THEN 0 ELSE l.loanCount END AS loanCount FROM ( SELECT Client.id AS client_id, Client.firstName, Client.lastName, Institution.id AS institution_id, Institution.name FROM Client, Institution ) AS d LEFT JOIN ( SELECT client_id, institution_id, COUNT(id) AS loanCount FROM Loan GROUP BY client_id, institution_id ) AS l ON d.client_id = l.client_id AND d.institution_id = l.institution_id

Edit: Includes a record for each institution

Edit: Spelling

@Strawberry It can probably be shortened, but this should get the job done. You are more than welcome to provide an answer.
Thank you for you answer! It works very well. I will accept this answer for now, unless someone can come up with something simpler.

Ravi Gehlot · Accepted Answer · 2017-06-05 23:17:22Z

SELECT loan.client_id, client.firstName, client.lastName, institution.name as financialInstitutionName, COUNT(loan.id) as loanCount FROM client INNER JOIN loan ON client.id = loan.client_id INNER JOIN institution ON loan.institution_id = institution.id GROUP BY client.id;

Not quite! I need one resulting row for every institution, whether or not the client has a loan with that institution.
@RichardHimself I see what you are trying to do, I will update my answer do accomplish this.

Collectives™ on Stack Overflow

SQL count rows of one table in relation to another table

2 Answers 2

3 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Related