Need to get a count of sectors respresented within JOINed tables

The query I have below is not working correctly.

select  H.Agency 
	,Count(CASE when R.Sector = 'Business' then 1 else 0 end) as [Business] ,Count(CASE when r.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer],Count(CASE when r.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident],Count(CASE when r.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based] 
	,Count(CASE when R.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare] ,Count(CASE when r.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies] ,Count(CASE when r.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement],Count(CASE when r.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
	,Count(CASE when r.Sector = 'Media' then 1 else 0 end) as [Media] ,Count(CASE when r.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian],Count(CASE when r.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic],Count(CASE when r.Sector = 'Schools' then 1 else 0 end) as [Schools],Count(CASE when r.Sector = 'Youth' then 1 else 0 end) as [Youth]

	from tblorghours H 
	inner join tblOrgRegistrations R on H.Regid = R.RegID
	inner join tblOrgActivities A on H.ActivityID = A.ActivityID
where H.activitydate > '7/1/17' and H.activitydate < '6/30/18' And A.ActivityName = 'Community Committee' group by H.Agency order by H.agency

Open in new window



I am trying to get a count of sectors that are represented.   Sectors is a column in tblOrgRegistrations.  tblOrgHours is a table for attendance.   tblOrgActivities provides information for activities.  As you can see I am trying to get information specified as follows:

where H.activitydate > '7/1/17' and H.activitydate < '6/30/18' And A.ActivityName = 'Community Committee' group by H.Agency order by H.agency

The problem is that the output shows the same value across.  I need to show how many sectors were represented by each agency for a specified time.

I have attached the output I'm getting which is wrong.  You will see by the same values going across.

Need help.
tblOrgHours.xlsx
tblOrgActivities.xlsx
tblOrgRegistrations.xlsx
Current-Output.--Not-correct.xlsx
al4629740Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
OK, well COUNT() is the wrong aggregation to use.

It needs to be SUM()

COUNT is going to count regardless of the value of "then 1 else 0" - either a 1 or a zero will increment a COUNT() aggregation....

See if this gets you closer
select  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
	inner join tblOrgActivities A on H.ActivityID = A.ActivityID
where H.activitydate >= '20170701' and H.activitydate < '20180701' And A.ActivityName = 'Community Committee' 
group by H.Agency order by H.agency

Open in new window

And for dates, always try to use 'yyyy-mm-dd' format and start should be greater than or equal to ">=" and end date should be less than "<" a day greater than the end date you want included - that way you will always capture any time occuring on the last day.

Does that make sense ?
0
 
SujithData ArchitectCommented:
You haven't provided the data that produces the current output. For example tblOrgRegistrations data is not complete. There is something wrong in your joins thats why the output is incorrect.

We dont need the whole data. Can you provide ONLY the input data contributing to one of the output lines?
0
 
Mark WillsTopic AdvisorCommented:
Your other option is to use PIVOT for example :
select *
from
( select H.agency, R.Sector, 1 as [Count]
  from tblorghours H 
  inner join tblOrgRegistrations R on H.Regid = R.RegID
  inner join tblOrgActivities A on H.ActivityID = A.ActivityID
  where H.activitydate >= '20170701' and H.activitydate < '20180701' And A.ActivityName = 'Community Committee' ) src
PIVOT
( sum([count]) for Sector in ([Business],[Civic-Volunteer],[Community Resident],[Faith Based],[Healthcare],[Human Support Agencies],[Law Enforcement],[Local Government],[Media],[Parent or Guardian],[Philanthropic],[Schools],[Youth]))pvt   

Open in new window

0
 
al4629740Author Commented:
Yes it makes sense on the first one.

With regards to Pivot, how does that function exactly work?  Does it take the values of the Sector column and then basically allow you to present it differently?
0
 
Mark WillsTopic AdvisorCommented:
Yep, the PIVOT is a aggregation type function, it basically aggregates a value for a src.column in a list of <resulting columns>

basic format is
SELECT *  -- and probably should spell out the list of columns
FROM
( <select <source data>, <col_to_display>, <col_to_aggregate>) as src
PIVOT
( aggregate(col_to_aggregate) for <col_to_display> in (list of resulting columns) ) pvt

Open in new window


So, it matches src.sector column value to one of those columns listed in the list of <resulting columns> (e.g. [Business],[Civic-Volunteer],[Community Resident], etc)  and aggregates SUM([count]) .The pivot then aligns that aggregated value under the corresponding <resulting columns>.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.