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.I'd use:SELECT Count(*)
FROM (SELECT COUNT(officeLink) AS theTotal
FROM tblOfficePivot
GROUP BY officeLink
HAVING COUNT(*) BETWEEN 1 AND 250
) AS a-PatP|||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|||What a bright boy
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
Wednesday, March 21, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment