
June 18th, 2012, 05:03 PM
|
|
Registered User
|
|
Join Date: Jun 2012
Posts: 2
Time spent in forums: 34 m 43 sec
Reputation Power: 0
|
|
|
Count query help!
Could some one help me with a query to do the below.
I have 2 tables
tblCustomer:
ID1 ID2 Fname Lname
A1 1 Simth M.
A1 2 Bob B.
A2 1 David B.
A3 2 Derek B.
A4 3 Tammy N.
A4 1 Ann J.
tblRequest:
ReID CustID1 CustID2
1 A1 1
2 A1 1
3 A2 1
4 A2 1
5 A3 2
6 A1 1
7 A2 1
8 A4 3
9 A1 2
10 A4 1
How can I get the result below:
CustID1 CustID2 Fname Lname TotalRequests
A1 1 Smith M. 3
A1 2 Bob B. 1
A2 1 David B. 3
A3 2 Derek B. 1
A4 3 Tammy N. 1
A4 1 Ann J. 1
I have tried:
SELECT
A.[Fname],
A.[Lname],
A.[ID1],
A.[ID2],
COUNT(B.[CustID1]) AS TotalRequest
FROM
[tblCustomer] AS A
INNER JOIN
[tblRequest] AS B
ON B.[CustID2] = A.[ID2]
WHERE A.[ID1] = B.[CustID1]
GROUP BY
A.[Fname],
A.[Lname],
A.[ID1],
A.[ID2]
ORDER BY
A.[ID2],
A.[ID1][/indent]But not getting the right results.
Please help!
Thanks in advanced.
|