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?
 
Russell FoxConnect With a Mentor Database 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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
All Courses

From novice to tech pro — start learning today.