SQL QUERY for sum loans per customer-CodePudding

enter image description hereQuery that returns all customers whose name contains the string "Will", and their associated total loan values. Loan Totals should be sorted from largest amount to smallest amount and the loans Totals column should be called "TotalLoanValue". Only one record per customer should be returned.

SELECT name,loan_amount FROM customers,loans WHERE name LIKE '%WILL%'

I have wrote the above query but having a hard time to figure out how to sum all the loan values per customer

CodePudding user response：

To say first things first:

If you want to ask further questions here, you should please read and follow this: How to create a good example instead of just adding a link.

Otherwise, you will be on risk that your questions will just be closed and you will never get an answer.

To answer your question:

We need to JOIN the two tables by their common column and then build the SUM of all loan amounts with a GROUP BY clause of the customer name.

I didn't follow your link because I wouldn't know if this is spam, so let's say the customer table has a column "id" and the loan table a column "customer_id".

Then your query will look like this:

SELECT c.name, SUM(l.loan_amount)
FROM customers c
JOIN loan l 
ON c.id = l.customer_id
WHERE c.name LIKE '%will%' 
GROUP BY c.name
ORDER BY SUM(l.loan_amount) DESC, c.name;

The ORDER BY clause makes sure to begin with the customer having the highest sum of loan amounts.

The "c.name" at the end of the ORDER BY clause could be removed if we don't care about the order if different customers have the same sum of loan amounts.

Otherwise, if we use the query as shown, the result will be sorted by the sum of loan amounts first and then with a second priority by the customer name, i.e. will sort customers having the identic sum of loan amounts by their name.

Try out with some sample data here: db<>fiddle

CodePudding user response：

Try this :

SELECT name, SUM(loan_amount) AS TotalLoanValue
FROM customers, loans
WHERE name LIKE '%WILL%'
GROUP BY name
ORDER BY TotalLoanValue DESC