SubQuery 1

[jchewsmith]

I need to write a subquery to pull a count of orders from one table to include in a query of customer sales

so customer table has customer name and order table has customer id and order id and I need a query to pull customer name and count of orders

 

[Andrzejek]

it is always a good idea to show some data in both tables, and what outcome you expect to see.
Please use TGML tags to format your data.


---- Andy

There is a great need for a sarcasm font.

 

[jchewsmith]

borrower table has loan# and borrower
loan table has loan#, lender#, state

I want to see:

Lender#, State, Loan#, count of borrowers

 

[Andrzejek]

Would that be your data?

[pre]
borrower table
loan# borrower
123 Joe
234 Sue
987 Jim

loan table
loan# lender# state
123 555 NY
234 666 NY
987 777 CT
[/pre]
If so, what would be the outcome?

If not, what would be the data that represents your situation?
Show some data that will create [tt]count of borrowers <> 1[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[jchewsmith]

borrower table
loan# borrower
123 Joe
123 Jane
123 John
234 Sue
234 Sid
987 Jim
987 Joe


loan table
loan# lender# state
123 555 NY
234 666 NY
987 777 CT

Outcome:
Loan# Lender# State #of Borr
123 555 NY 3
234 666 NY 2
987 777 CT 2

 

[Andrzejek]

I would try:

[tt]SELECT L.Loan#, L.Lender#, L.State,
(SELECT COUNT(B.*) From borrower B Where B.loan# = L.loan# ) AS "#of Borr"
FROM loan L
[/tt]


---- Andy

There is a great need for a sarcasm font.