select
  State,
  UserCount = count(distinct c.UserID),
  ComponentCount = count(u.LocationID)
from
  #Locations l
  inner join #Users u on u.LocationID = l.LocationID
  inner join #Components c on c.UserID = u.UserID
group by
  state

State UserCount   ComponentCount 
----- ----------- -------------- 
MD    2           6
VA    2           4

Thanks. Just what I was looking for. It cleared up an issue I was currently working on.

Jim Crosby

In example 2, couldn't this be used also?

DECLARE @orderby int

SET @orderby = 1

SELECT l.State, ComponentName
FROM #Locations l
INNER JOIN #Users u ON l.LocationID = u.LocationID
INNER JOIN #Components c ON c.UserID = u.UserID
GROUP By l.State, ComponentName
ORDER BY CASE @orderby
             WHEN 1 THEN l.State
             WHEN 2 THEN ComponentName
             END

I do like the changing of the order by with a variable.  Definitely a nice little trick.

Pat

Robert,

As this thread shows there are often several valid approaches to generating a desired result set. In one of my applications, users often wish to include/exclude all members of a household when one or more member qualifies based on selected criteria. Below is a simple example that uses derived tables to find members of a household with an opinion ='No' where at least one other household member has an opinion of 'Yes':

-- paste into QA
CREATE TABLE test
(ID bigint identity primary key, FirstName varchar(15), LastName varchar(20), Household char(1), Opinion varchar(10))

INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Joe', 'Jones', 'A','Yes')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Sam', 'Jones', 'A','No')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Mary', 'Jones', 'A','Yes')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Joe', 'Smith', 'B','Yes')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Sam', 'Smith', 'B','No')
INSERT INTO test (FirstName, LastName, Household, Opinion)
VALUES ('Mary', 'Smith', 'B','No')

/* statement shows fields from both 'test' and derived table
'hh' to illustrate the technique. Note that the DISTINCT keyword is used because each member of a household in 'hh' with an option of 'Yes' returns all records from 'test' where Opinion='No', resulting in duplicates.
*/

SELECT DISTINCT * FROM test
INNER JOIN test as hh ON test.Household = hh.Household
WHERE hh.Opinion='Yes' and test.Opinion='No'

-- David Terrie