×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SubQuery

SubQuery

(OP)
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

RE: SubQuery

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.

RE: SubQuery

(OP)
borrower table has loan# and borrower
loan table has loan#, lender#, state

I want to see:

Lender#, State, Loan#, count of borrowers

RE: SubQuery

Would that be your data?

borrower table 
loan#  borrower
123    Joe
234    Sue
987    Jim

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

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


---- Andy

There is a great need for a sarcasm font.

RE: SubQuery

(OP)
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

RE: SubQuery

I would try:

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


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close