Adding existing output column to a current query

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

Open in new window

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.

Mark WillsTopic AdvisorCommented:
You can use a window function ie OVER() for that requirement because all elements for the calculation are included in the GROUP BY - which means no conflict with that confounding 'Not part of Aggregate' error we sometimes see....

Please try :
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
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount]
	   ,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
       ,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

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
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:
Spot on.  

Question on line 18.  Could you explain why you were using 'partition by'
0
Mark WillsTopic AdvisorCommented:
Yep, the Partition By is a bit like a group by. Each time the partition changes, the count gets reset for the new partition

Check out that link, it describes (in typical MS speak) what it is doing.
0
al4629740Author Commented:
Thank you Mark
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
Microsoft SQL Server

From novice to tech pro — start learning today.