Loop in a sql query

Question

In my Sql database, I have tables

1 Account_Customer -AccountID -CustomerID -Account_CustomerID

2 Accounts -AccountID -Balance

3 Customers -CustomerID -Name -Sex -Age

4 Loans -LoanID -Amount -BranchName

5 Loan_Customer -Loan_CustomerID -LoanID -CustomerID

and I want to write a stored procedure for Listing customers together with their number of accounts, total account balances, number of loans, and total loan amounts, living in a given city, having given sex and age, and having accounts and/or loans in a given branch.

I can do the number of accounts and total account balances in my program but I need a stored procedure for my assignment.

Any help would be greatly appreciated.

You want all that in one query? Or several separate queries? — Mark Byers
– Mark Byers, Commented Dec 31, 2009 at 1:15
I think you're missing a field in the Accounts table for what you want to do. You said you want to filter where customers have an account in a certain branch, but there is no branch information associated with the account... or if there is, I can't see it... — Rory
– Rory, Commented Dec 31, 2009 at 1:18
There is nothing in the provided tables that tells us what city a customer lives in — Gabriel McAdams
– Gabriel McAdams, Commented Dec 31, 2009 at 1:18
Ok, I just didnt copy all information, I need 3 different stored procedures 1.for a given city 2.given sex and age 3.given accounts or loans in a branch but one example of a query loop would help me enought ! — Kubi
– Kubi, Commented Dec 31, 2009 at 1:20
i believe this can be done with a single query, joining all tables together — Dapeng
– Dapeng, Commented Dec 31, 2009 at 1:20

Rory · Accepted Answer · 2009-12-31 01:44:44Z

1

Okay, lets give this a bash (although I still think we're missing a few pieces)

CREATE PROCEDURE SelectCustomerDetailsBySex
  @Sex <your data type here>
AS
BEGIN
  SELECT cus.CustomerID,
    cus.Name,
    COUNT(acc.AccountID) AS AccountCount,
    SUM(acc.Balance) AS AccountBalance,
    COUNT(loa.LoanID) AS LoanCount,
    SUM(loa.Amount) AS LoanTotal
  FROM Customers cus
  LEFT OUTER JOIN Account_Customer ac ON cus.CustomerID = ac.CustomerID
  LEFT OUTER JOIN Accounts acc ON ac.AccountID = acc.AccountID
  LEFT OUTER JOIN Loan_Customer lc ON cus.CustomerID = lc.CustomerID
  LEFT OUTER JOIN Loans loa ON lc.LoanID = loa.LoanID
  WHERE cus.Sex = @Sex
  GROUP BY cus.CustomerID,
    cus.Name;
END

Will that do as an example, or would you like me to do another?

edited Dec 31, 2009 at 1:44

answered Dec 31, 2009 at 1:33

Rory

2,7421 gold badge20 silver badges11 bronze badges

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

3 Comments

Kubi Over a year ago

not necessary. thank you! but I get an error : Msg 8120, Level 16, State 1, Line 1 Column 'Customers.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Rory Over a year ago

No problem! Now I'm going to bed, it's almost 4am here. Everybody have a safe and fun New Years!

Kubi Over a year ago

stackoverflow.com/questions/1989340/join-query-returns-me-wrong I am still having a problem with the same assignment, can you help me ?

Collectives™ on Stack Overflow

Loop in a sql query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related