Find all sectors represented per each Unique Agency on a table

I need help fast.  I need an SQL query for the following request

In the attached table I highlighted specific columns that represent sectors.  I need to know for each Unique Agency, How many of these sectors are represented.  For example, if the agency "Albany Park" has at least one row with the sector Police then that counts as one sector represented for the time period.  I need to know for each of these Agencies which sectors were at least represented at some point or basically the ones that have a "1" in it.

Please help
tblOrgHours.xlsx
al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
You can just group by the AgencyName, sum the fields, and then use a CASE statement to say "if >1 then 1 else 0":
--	----------------------------------------------------------------------------
--	Setting up your sample data...
	DECLARE @TestData TABLE([Agency] VARCHAR(255), [CommunityCommittee] INT, [YouthCommittee] INT, [Parentcheck] INT, [CommunityResident] INT, [Asian] INT, [African-American] INT, [Caucasian] INT, [Native-American] INT, [Multi-Racial] INT, [Latino-Hispanic] INT, [Male] INT, [Female] INT, [Business] INT, [Civic-Volunteer] INT, [Community Resident] INT, [Faith Based] INT, [Healthcare] INT, [Human Support Agencies] INT, [Law Enforcement] INT, [Local Government] INT, [Media] INT, [Parent or Guardian] INT, [Philanthropic] INT, [Schools] INT, [Youth] INT)
	INSERT INTO @TestData
	(
	Agency, CommunityCommittee, YouthCommittee, Parentcheck, CommunityResident, Asian, [African-American], Caucasian, [Native-American], [Multi-Racial], [Latino-Hispanic], Male, Female, Business, [Civic-Volunteer], [Community Resident], [Faith Based], Healthcare, [Human Support Agencies], [Law Enforcement], [Local Government], Media, [Parent or Guardian], Philanthropic, Schools, Youth )
	VALUES
	('Administrator', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Administrator', 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Administrator', 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Administrator', 1, 2, 0, 0, 1, 2, 0, 0, 0, 0, 3, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1)
	,('Administrator', 1, 1, 0, 0, 1, 1, 0, 0, 0, 0, 2, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1)
	,('Administrator', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1)
	,('Administrator', 0, 1, 0, 0, 2, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Administrator', 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Administrator', 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Administrator', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1)
	,('Administrator', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1)
	,('Administrator', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Albany Park - Community Committee', 4, 0, 0, 0, 0, 0, 0, 0, 0, 4, 2, 2, 0, 0, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Albany Park - Community Committee', 4, 0, 0, 0, 0, 0, 0, 0, 0, 4, 2, 2, 0, 0, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Chicago Heights - LUCHA', 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Chicago Heights - LUCHA', 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Chicago Heights - LUCHA', 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Chicago Heights - LUCHA', 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Cicero - Area Project', 5, 1, 0, 0, 0, 0, 0, 0, 0, 6, 4, 2, 1, 0, 1, 1, 0, 1, 0, 1, 0, 0, 0, 0, 0)
	,('Cicero - Area Project', 6, 1, 0, 0, 0, 0, 0, 0, 0, 7, 4, 3, 0, 0, 1, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0)
	,('Cicero - Area Project', 6, 1, 0, 0, 0, 0, 0, 0, 0, 7, 4, 3, 0, 0, 1, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0)
	,('East Garfield Park - Westside Community', 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Ford Heights - Community Committee', 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0)
	,('Ford Heights - Community Committee', 2, 0, 0, 0, 0, 2, 0, 0, 0, 0, 2, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0)
	,('Ford Heights - Community Committee', 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0)
	,('Ford Heights - Community Committee', 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0)
	,('Ford Heights - Community Committee', 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Ford Heights - Community Committee', 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	,('Ford Heights - Community Committee', 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

--	----------------------------------------------------------------------------
--	Actual query:
SELECT 
	Agency
	, CommunityCommittee = CASE WHEN SUM(CommunityCommittee) > 1 THEN 1 ELSE 0 END
	, YouthCommittee = CASE WHEN SUM(YouthCommittee) > 1 THEN 1 ELSE 0 END
	, Parentcheck = CASE WHEN SUM(Parentcheck) > 1 THEN 1 ELSE 0 END
	, CommunityResident = CASE WHEN SUM(CommunityResident) > 1 THEN 1 ELSE 0 END
	, Asian = CASE WHEN SUM(Asian) > 1 THEN 1 ELSE 0 END
	, [African-American] = CASE WHEN SUM([African-American]) > 1 THEN 1 ELSE 0 END
	, Caucasian = CASE WHEN SUM(Caucasian) > 1 THEN 1 ELSE 0 END
	, [Native-American] = CASE WHEN SUM([Native-American]) > 1 THEN 1 ELSE 0 END
	, [Multi-Racial] = CASE WHEN SUM([Multi-Racial]) > 1 THEN 1 ELSE 0 END
	, [Latino-Hispanic] = CASE WHEN SUM([Latino-Hispanic]) > 1 THEN 1 ELSE 0 END
	, Male = CASE WHEN SUM(Male) > 1 THEN 1 ELSE 0 END
	, Female = CASE WHEN SUM(Female) > 1 THEN 1 ELSE 0 END
	, Business = CASE WHEN SUM(Business) > 1 THEN 1 ELSE 0 END
	, [Civic-Volunteer] = CASE WHEN SUM([Civic-Volunteer]) > 1 THEN 1 ELSE 0 END
	, [Community Resident] = CASE WHEN SUM([Community Resident]) > 1 THEN 1 ELSE 0 END
	, [Faith Based] = CASE WHEN SUM([Faith Based]) > 1 THEN 1 ELSE 0 END
	, Healthcare = CASE WHEN SUM(Healthcare) > 1 THEN 1 ELSE 0 END
	, [Human Support Agencies] = CASE WHEN SUM([Human Support Agencies]) > 1 THEN 1 ELSE 0 END
	, [Law Enforcement] = CASE WHEN SUM([Law Enforcement]) > 1 THEN 1 ELSE 0 END
	, [Local Government] = CASE WHEN SUM([Local Government]) > 1 THEN 1 ELSE 0 END
	, Media = CASE WHEN SUM(Media) > 1 THEN 1 ELSE 0 END
	, [Parent or Guardian] = CASE WHEN SUM([Parent or Guardian]) > 1 THEN 1 ELSE 0 END
	, Philanthropic = CASE WHEN SUM(Philanthropic) > 1 THEN 1 ELSE 0 END
	, Schools = CASE WHEN SUM(Schools) > 1 THEN 1 ELSE 0 END
	, Youth = CASE WHEN SUM(Youth) > 1 THEN 1 ELSE 0 END
FROM @TestData
GROUP BY Agency

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
Does this format work also?

select distinct H.agency, 
SUM(CASE when R.Sector = 'Business' then 1 else 0 end) as [Business] ,SUM(CASE when R.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer],SUM(CASE when R.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident],SUM(CASE when R.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based] ,SUM(CASE when R.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare] ,SUM(CASE when R.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies] ,SUM(CASE when R.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement],SUM(CASE when R.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when R.Sector = 'Media' then 1 else 0 end) as [Media] ,SUM(CASE when R.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian],SUM(CASE when R.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic],SUM(CASE when R.Sector = 'Schools' then 1 else 0 end) as [Schools],SUM(CASE when R.Sector = 'Youth' then 1 else 0 end) as [Youth]
from tblorghours H inner join tblOrgRegistrations R on H.Regid = R.RegID 
where H.activitydate > '7/1/17' and activitydate < '6/30/18' group by h.agency order by h.agency

Open in new window

0
al4629740Author Commented:
Actually yours is better
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

al4629740Author Commented:
select  H.agency 
	, Business = CASE WHEN SUM(Business) > 1 THEN 1 ELSE 0 END
	, [Civic-Volunteer] = CASE WHEN SUM([Civic-Volunteer]) > 1 THEN 1 ELSE 0 END
	, [Community Resident] = CASE WHEN SUM([Community Resident]) > 1 THEN 1 ELSE 0 END
	, [Faith Based] = CASE WHEN SUM([Faith Based]) > 1 THEN 1 ELSE 0 END
	, Healthcare = CASE WHEN SUM(Healthcare) > 1 THEN 1 ELSE 0 END
	, [Human Support Agencies] = CASE WHEN SUM([Human Support Agencies]) > 1 THEN 1 ELSE 0 END
	, [Law Enforcement] = CASE WHEN SUM([Law Enforcement]) > 1 THEN 1 ELSE 0 END
	, [Local Government] = CASE WHEN SUM([Local Government]) > 1 THEN 1 ELSE 0 END
	, Media = CASE WHEN SUM(Media) > 1 THEN 1 ELSE 0 END
	, [Parent or Guardian] = CASE WHEN SUM([Parent or Guardian]) > 1 THEN 1 ELSE 0 END
	, Philanthropic = CASE WHEN SUM(Philanthropic) > 1 THEN 1 ELSE 0 END
	, Schools = CASE WHEN SUM(Schools) > 1 THEN 1 ELSE 0 END
	, Youth = CASE WHEN SUM(Youth) > 1 THEN 1 ELSE 0 END

	from tblorghours H inner join tblOrgRegistrations R on H.Regid = R.RegID 
where H.activitydate > '7/1/17' and activitydate < '6/30/18' group by h.agency order by h.agency

Open in new window


Errors I get are:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Business'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Civic-Volunteer'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Community Resident'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Faith Based'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'Healthcare'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Human Support Agencies'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'Law Enforcement'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Local Government'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Media'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Parent or Guardian'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Philanthropic'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Schools'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Youth'.
0
al4629740Author Commented:
Those are the actual column names though
0
al4629740Author Commented:
this was correct for what I presented however I have stated the wrong question.  I am re posting the correct question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.