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

2

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

edited Jun 5, 2017 at 23:37

answered Jun 5, 2017 at 23:23

David Lee

2,1301 gold badge20 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Strawberry Over a year ago

It looks like more query than is strictly necessary

David Lee Over a year ago

@Strawberry It can probably be shortened, but this should get the job done. You are more than welcome to provide an answer.

RichardHimself Over a year ago

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

1

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;

answered Jun 5, 2017 at 23:17

Ravi Gehlot

1,16911 silver badges17 bronze badges

2 Comments

RichardHimself Over a year ago

Not quite! I need one resulting row for every institution, whether or not the client has a loan with that institution.

David Lee Over a year ago

@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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related