Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    41

    Question Unanswered: Problem Getting COUNT() to return the value I'm after

    Hello. I have a database with the following pivot table which has one record linking each employee in the company to the offices they work at. I'm using a pivot table instead of a direct reference because some employees work at more than one office.

    Table Def: tblOfficePivot
    -----------------------
    key <- PK ID
    officeLink <- Link to office record
    employeeLink <- link to employee record.

    I want to write a select I can use to return the total number of offices with an emplyee population between X and Y (eg. 1-250, 251-500, 501-1000, etc.)

    I can write a query which return one result for each office that falls into the range of employees I define. This query looks like this:

    SELECT COUNT(officeLink) AS theTotal
    FROM tblOfficePivot
    GROUP BY officeLink
    HAVING COUNT(*) BETWEEN 1 AND 250

    The above query returns 1 record for each office with between 1 and 250 employees - the result being the employee count. So, if 2 offices fell into this category, A & B, having 50 and 123 employees respectively, the result set would look Like this:

    theTotal
    --------
    1. 50
    2. 123

    What I want instead is the total number of offices, in this case 2.

    Is there a way to do this without the COMPUTE clause?

    Thanks for any suggestions, this one is driving me crazy.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT Count(*)
       FROM (SELECT COUNT(officeLink) AS theTotal
          FROM tblOfficePivot
          GROUP BY officeLink
          HAVING COUNT(*) BETWEEN 1 AND 250
       ) AS a
    -PatP

  3. #3
    Join Date
    Feb 2003
    Posts
    41

    Talking Thanks Pat!

    Thanks Pat, that worked a charm. Hadn't written a query in that format until now. Definitely something that will come in handy in the future.

    Regards, Matt

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What a bright boy

    Code:
    USE Northwind
    GO
    
    SELECT Count(*)
       FROM (SELECT CustomerId, COUNT(CustomerId)TotalPerId, count(*) AS theTotal
          FROM Orders
          GROUP BY CustomerId
          HAVING COUNT(*) BETWEEN 1 AND 10
       ) AS a
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •