#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep 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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    The query looks alright. In what sense is the result incorrect?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by swampBoogie
    The query looks alright. In what sense is the result incorrect?
    My request tbl contents 900,000 records but it returns only 400K. Something is missing.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    I don't understand what you mean by missing. Compare with your example, which have 10 rows in the request table but six rows in the result.

    Since there are duplicate records, with regard to custid1 and custid2 in the request table these will be aggregated as one row in the result.

    Or do you mean that there are 900,000 records in the customer table? If so, what should be shown for customers without requests?
    Last edited by swampBoogie; June 20th, 2012 at 04:49 AM.

IMN logo majestic logo threadwatch logo seochat tools logo