In the following code, I have need to develop an output column that shows the number of [ActivityName] for each activity. That new output column would be called [ActivityCount] This needs to be done without losing the existing output. Essentially if basketball is listed 5 times, then each row for ActivityCount would say 5 in it.

```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency, H.Classification, H.Objectives, H.Advocate, H.AdvocacyType, H.ActivityID, H.RegID, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Administrator'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180101'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Advocate
,H.AdvocacyType
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity] -- total distinct ID's
,Count(H.RegID) as [# individuals]
,SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]
,SUM(CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]
,SUM(CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]
,SUM(CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]
,SUM(CASE when R.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]
,SUM(CASE when R.Board = '1' then 1 else 0 end) as [CommunityCommittee]
,SUM(CASE when R.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee]
,SUM(CASE when R.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
,SUM(CASE when R.CommunityResident = '1' then 1 else 0 end) as [CommunityResident]
,SUM(CASE when R.Race = 'Asian' then 1 else 0 end) as [Asian]
,SUM(CASE when R.Race = 'African-American' then 1 else 0 end) as [African-American]
,SUM(CASE when R.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
,SUM(CASE when R.Race = 'Native-American' then 1 else 0 end) as [Native-American]
,SUM(CASE when R.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
,SUM(CASE when R.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
,SUM(CASE when R.Gender = 'Male' then 1 else 0 end) as [Male]
,SUM(CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]
,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 CTE_Hours H -- notice how we can now select from the named query CTE_Hours
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID
inner join (select AgencyID, ActivityID, count(distinct Regid) as [Tot Individuals for Activity] from CTE_Hours group by AgencyID,Activityid) I on I.AgencyID = H.AgencyID and I.Activityid = H.ActivityId
group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3
```

tblOrgHours.xlsx
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.

Please try :

Open in new window

Have a read of : https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017#b-using-the-over-clause-with-aggregate-functions