Jump to content

count query


inou
 Share

Recommended Posts

Could some one help me with a query to do the below.I have 2 tablestblCustomer:ID1 ID2 Fname LnameA1 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 CustID21 A1 12 A1 13 A2 14 A2 15 A3 26 A1 17 A2 18 A4 39 A1 210 A4 1 How can I get the result below:CustID1 CustID2 Fname Lname TotalRequestsA1 1 Smith M. 3A1 2 Bob B. 1A2 1 David B. 3A3 2 Derek B. 1A4 3 Tammy N. 1A4 1 Ann J. 1I have tried:SELECTA.[Fname],A.[Lname],A.[iD1],A.[iD2],COUNT(B.[CustID1]) AS TotalRequestFROM[tblCustomer] AS AINNER JOIN[tblRequest] AS BON B.[CustID2] = A.[iD2]WHERE A.[iD1] = B.[CustID1]GROUP BYA.[Fname],A.[Lname],A.[iD1],A.[iD2]ORDER BYA.[iD2],A.[iD1] But not getting the right results.Please help!Thanks in advanced.

Edited by inou
Link to comment
Share on other sites

You're grouping the requests, it's not going to return 1 row for every request. If you think there are requests that it is not counting, an inner join will leave out records that don't have a match in both tables. So that would mean that the customer that the request is for got deleted.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...