'Count how many records does each user have in the second table
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?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
