count(d.deal_id) as dealCount
from applicants a
inner join deals d on d.company_id = a.company_id
group by a.company_id, a.applicantid, d.deal_id
order by a.company_id
maybe?
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:11 PM
To: CF-Talk
Subject: SQL Puzzle?
I have two tables... APPLICANTS and DEALS. There can be multiple deals per
applicant but only one applicant per deal.
SELECT COMPANY_ID, COUNT(APPLICANT_ID)
FROM APPLICANTS
GROUP BY COMPANY_ID
Results:
1 3
2 9
3 7
SELECT COMPANY_ID, COUNT(DEAL_ID)
FROM DEALS
GROUP BY COMPANY_ID
Results:
1 3
2 11
3 8
What I'm trying to get is:
1 3 3
2 9 11
3 7 8
My problem is actually a little harder than this but if someone could tell
me HOW to get my desired results above in a single SQL query then I could
probably figure out the rest.
-Novak
________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
