Count how many records does each user have in the second table-CodePudding

I have 2 Tables (The relevant ones for this question). One for Customers, second for loans.
What I am trying to accomplish is to get each customer's information, and in addition to that how many loans he toked.

My User Table

 -------- ------- ------- ----- 
| cst_id | fname | lname | age |
 -------- ------- ------- ----- 
|      1 | xx    | yy    |  11 |
|      2 | xx    | yy    |  22 |
|      3 | xx    | yy    |  33 |
|      4 | xx    | yy    |  44 |
|      5 | xx    | yy    |  55 |
 -------- ------- ------- -----

Loan Table

 --------- ----------------- 
| cust_id | loan_variant_id |
 --------- ----------------- 
|       1 |               1 |
|       1 |               2 |
|       2 |               3 |
|       2 |               1 |
|       3 |               1 |
 --------- -----------------

We see that customers with ID 1 have 2 registered loans.

How the final table should look like

you can ignore the new column "name" because it is irrelevant to the question.

 -------- ------- ------- ----- ------- ------------ 
| cst_id | fname | lname | age | name  | loan_count |
 -------- ------- ------- ----- ------- ------------ 
|      1 | xx    | yy    |  11 | xx yy |          2 |
|      2 | xx    | yy    |  22 | xx yy |          2 |
|      3 | xx    | yy    |  33 | xx yy |          1 |
|      4 | xx    | yy    |  44 | xx yy |          0 |
|      5 | xx    | yy    |  55 | xx yy |          0 |
 -------- ------- ------- ----- ------- ------------

My SQL code

SELECT
    *,
    CONCAT(fname, ' ', lname) AS name,
    COUNT(##some magic code please, at least what i think##) AS loan_count
FROM
    t_cst

Now I know that via this query I can accomplish what I want (counting How many loans are token by each user) in the t_loans table. However, I am kind of stuck on how to use both queries together.

SELECT
    COUNT(cust_id) AS loansCountForEachCst
FROM
    t_loans
GROUP BY
    (cust_id)

Any Ideas?

CodePudding user response：

We can use a simple GROUP BY and an OUTER JOIN to include all the customers even if they have no loans:

SELECT
    c.cst_id,
    c.fname,
    c.lname,
    c.age,
    CONCAT(c.fname, ' ', c.lname) AS name,
    COUNT(l.loan_variant_id) AS loan_count
FROM
    t_cst c
LEFT OUTER JOIN 
    t_loans l ON c.cst_id = l.cust_id
GROUP BY
    c.cst_id, c.fname, c.lname, c.age

The catch is that we have to capture all the common outputs in the GROUP BY clause.

See this fiddle: http://sqlfiddle.com/#!9/bd7ee73/2