Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Currently I have the following query that lists each activity on a separate line. What modifications do I need to make in order to have just one line that tells all the SUMs for the specified time. It no longer needs to group the ActivityName, Classifciation, activityDate, Objectives, Hours, Activitycount, Narrative, Outcome, Strategy.

```
;With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.Board, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , 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.Fiscal = 2018 ) select H.Agency ,A.ActivityName,H.Classification,H.ActivityDate,H.Objectives,H.Hours,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],MAX(I.[Tot Ages 11-12]) as [Tot Ages 11-12],max(I.[Tot Ages 13-17]) as [Tot Ages 13-17],max(I.[Tot Ages 18-20]) as [Tot Ages 18-20],max(I.[Tot Ages 21-24]) as [Tot Ages 21-24]
,Count(H.RegID) as [# individuals],H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther,SUM(CASE when H.AgeCurrent >= 11 and H.AgeCurrent <= 12 then 1 else 0 end) as [Ages 11-12],SUM(CASE when H.AgeCurrent >= 13 and H.AgeCurrent <= 17 then 1 else 0 end) as [Ages 13-17] ,SUM(CASE when H.AgeCurrent >= 18 and H.AgeCurrent <= 20 then 1 else 0 end) as [Ages 18-20] ,SUM(CASE when H.AgeCurrent >= 21 and H.AgeCurrent <= 24 then 1 else 0 end) as [Ages 21-24] ,SUM(CASE when H.Board = '1' then 1 else 0 end) as [CommunityCommittee] ,SUM(CASE when H.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee] ,SUM(CASE when H.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
,SUM(CASE when H.CommunityResident = '1' then 1 else 0 end) as [CommunityResident],SUM(CASE when H.Race = 'Asian' then 1 else 0 end) as [Asian],SUM(CASE when H.Race = 'African-American' then 1 else 0 end) as [African-American],SUM(CASE when H.Race = 'Caucasian' then 1 else 0 end) as [Caucasian],SUM(CASE when H.Race = 'Native-American' then 1 else 0 end) as [Native-American],SUM(CASE when H.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial],SUM(CASE when H.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic],SUM(CASE when H.Gender = 'Male' then 1 else 0 end) as [Male],SUM(CASE when H.Gender = 'Female' then 1 else 0 end) as [Female]
,SUM(CASE when H.Sector = 'Business' then 1 else 0 end) as [Business] ,SUM(CASE when H.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer],SUM(CASE when H.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident],SUM(CASE when H.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based] ,SUM(CASE when H.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare] ,SUM(CASE when H.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies] ,SUM(CASE when H.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement],SUM(CASE when H.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when H.Sector = 'Media' then 1 else 0 end) as [Media] ,SUM(CASE when H.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian],SUM(CASE when H.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic],SUM(CASE when H.Sector = 'Schools' then 1 else 0 end) as [Schools],SUM(CASE when H.Sector = 'Youth' then 1 else 0 end) as [Youth] from CTE_Hours H inner join tblOrgActivities A on H.ActivityID = A.ActivityID cross apply (select AgencyID, ActivityID, count(Regid) as [Tot Individuals for Activity] ,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 12 then 1 else 0 end) as [Tot Ages 11-12],sum(CASE when AgeCurrent >= 13 and AgeCurrent <= 17 then 1 else 0 end) as [Tot Ages 13-17]
,SUM(CASE when AgeCurrent >= 18 and AgeCurrent <= 20 then 1 else 0 end) as [Tot Ages 18-20],sum(CASE when AgeCurrent >= 21 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 21-24] from (select distinct agencyid, activityID, regid, agecurrent from CTE_Hours) d Where A.ActivityName = 'Community Committee' And d.AgencyID = h.AgencyID And d.ActivityId = h.ActivityId group by agencyID, ActivityID) I group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
```

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

```
;With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.Board, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , 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.Fiscal = 2018 )
select H.Agency ,A.ActivityName,H.Classification,H.ActivityDate,H.Objectives,H.Hours
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName) as [ActivityCount]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity],MAX(I.[Tot Ages 11-12]) as [Tot Ages 11-12],max(I.[Tot Ages 13-17]) as [Tot Ages 13-17],max(I.[Tot Ages 18-20]) as [Tot Ages 18-20],max(I.[Tot Ages 21-24]) as [Tot Ages 21-24]
,Count(H.RegID) as [# individuals],H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther,SUM(CASE when H.AgeCurrent >= 11 and H.AgeCurrent <= 12 then 1 else 0 end) as [Ages 11-12],SUM(CASE when H.AgeCurrent >= 13 and H.AgeCurrent <= 17 then 1 else 0 end) as [Ages 13-17] ,SUM(CASE when H.AgeCurrent >= 18 and H.AgeCurrent <= 20 then 1 else 0 end) as [Ages 18-20] ,SUM(CASE when H.AgeCurrent >= 21 and H.AgeCurrent <= 24 then 1 else 0 end) as [Ages 21-24] ,SUM(CASE when H.Board = '1' then 1 else 0 end) as [CommunityCommittee] ,SUM(CASE when H.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee] ,SUM(CASE when H.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
,SUM(CASE when H.CommunityResident = '1' then 1 else 0 end) as [CommunityResident],SUM(CASE when H.Race = 'Asian' then 1 else 0 end) as [Asian],SUM(CASE when H.Race = 'African-American' then 1 else 0 end) as [African-American],SUM(CASE when H.Race = 'Caucasian' then 1 else 0 end) as [Caucasian],SUM(CASE when H.Race = 'Native-American' then 1 else 0 end) as [Native-American],SUM(CASE when H.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial],SUM(CASE when H.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic],SUM(CASE when H.Gender = 'Male' then 1 else 0 end) as [Male],SUM(CASE when H.Gender = 'Female' then 1 else 0 end) as [Female]
,SUM(CASE when H.Sector = 'Business' then 1 else 0 end) as [Business] ,SUM(CASE when H.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer],SUM(CASE when H.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident],SUM(CASE when H.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based] ,SUM(CASE when H.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare] ,SUM(CASE when H.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies] ,SUM(CASE when H.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement],SUM(CASE when H.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when H.Sector = 'Media' then 1 else 0 end) as [Media] ,SUM(CASE when H.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian],SUM(CASE when H.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic],SUM(CASE when H.Sector = 'Schools' then 1 else 0 end) as [Schools],SUM(CASE when H.Sector = 'Youth' then 1 else 0 end) as [Youth]
, COUNT(*) OVER () cnt
from CTE_Hours H
inner join tblOrgActivities A on H.ActivityID = A.ActivityID
cross apply (
select AgencyID, ActivityID, count(Regid) as [Tot Individuals for Activity]
,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 12 then 1 else 0 end) as [Tot Ages 11-12]
,sum(CASE when AgeCurrent >= 13 and AgeCurrent <= 17 then 1 else 0 end) as [Tot Ages 13-17]
,SUM(CASE when AgeCurrent >= 18 and AgeCurrent <= 20 then 1 else 0 end) as [Tot Ages 18-20]
,sum(CASE when AgeCurrent >= 21 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 21-24]
from (select distinct agencyid, activityID, regid, agecurrent from CTE_Hours) d
Where A.ActivityName = 'Community Committee'
And d.AgencyID = h.AgencyID And d.ActivityId = h.ActivityId
group by agencyID, ActivityID) I
group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
```

And if that is the reason why you no longer need to group by ActivityName (plus others) then make it part of the join on tblOrgActivities

That way the count doesnt have to change....

Then it is a simple matter of commenting out, or removing the columns no longer needed.

```
;With CTE_Hours as
( select distinct H.AgencyID, H.Agency, H.ActivityID, H.RegID
, R.AgeCurrent, R.Board, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector, 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.Fiscal = 2018
)
select H.Agency
,A.ActivityName
-- ,H.Classification
-- ,H.ActivityDate
-- ,H.Objectives
-- ,H.Hours
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount]
-- or if removing activityName from group by then you have to do
--,count(distinct A.Activityid) as [ActivityCount]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,MAX(I.[Tot Ages 11-12]) as [Tot Ages 11-12]
,max(I.[Tot Ages 13-17]) as [Tot Ages 13-17]
,max(I.[Tot Ages 18-20]) as [Tot Ages 18-20]
,max(I.[Tot Ages 21-24]) as [Tot Ages 21-24]
,Count(H.RegID) as [# individuals]
--,H.[ActivityType]
--,H.ActivityOther
--,H.Narrative
--,H.Outcome
--,H.Duration
--,H.Frequency
--,H.Strategy
--,H.Need
--,H.NeedOther
,SUM(CASE when H.AgeCurrent >= 11 and H.AgeCurrent <= 12 then 1 else 0 end) as [Ages 11-12]
,SUM(CASE when H.AgeCurrent >= 13 and H.AgeCurrent <= 17 then 1 else 0 end) as [Ages 13-17]
,SUM(CASE when H.AgeCurrent >= 18 and H.AgeCurrent <= 20 then 1 else 0 end) as [Ages 18-20]
,SUM(CASE when H.AgeCurrent >= 21 and H.AgeCurrent <= 24 then 1 else 0 end) as [Ages 21-24]
,SUM(CASE when H.Board = '1' then 1 else 0 end) as [CommunityCommittee]
,SUM(CASE when H.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee]
,SUM(CASE when H.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
,SUM(CASE when H.CommunityResident = '1' then 1 else 0 end) as [CommunityResident]
,SUM(CASE when H.Race = 'Asian' then 1 else 0 end) as [Asian]
,SUM(CASE when H.Race = 'African-American' then 1 else 0 end) as [African-American]
,SUM(CASE when H.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
,SUM(CASE when H.Race = 'Native-American' then 1 else 0 end) as [Native-American]
,SUM(CASE when H.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
,SUM(CASE when H.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
,SUM(CASE when H.Gender = 'Male' then 1 else 0 end) as [Male]
,SUM(CASE when H.Gender = 'Female' then 1 else 0 end) as [Female]
,SUM(CASE when H.Sector = 'Business' then 1 else 0 end) as [Business]
,SUM(CASE when H.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer]
,SUM(CASE when H.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident]
,SUM(CASE when H.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based]
,SUM(CASE when H.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare]
,SUM(CASE when H.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies]
,SUM(CASE when H.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement]
,SUM(CASE when H.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when H.Sector = 'Media' then 1 else 0 end) as [Media]
,SUM(CASE when H.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian]
,SUM(CASE when H.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic]
,SUM(CASE when H.Sector = 'Schools' then 1 else 0 end) as [Schools]
,SUM(CASE when H.Sector = 'Youth' then 1 else 0 end) as [Youth]
from CTE_Hours H
inner join tblOrgActivities A on H.ActivityID = A.ActivityID and A.ActivityName = 'Community Committee'
cross apply (select AgencyID, ActivityID, count(Regid) as [Tot Individuals for Activity]
,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 12 then 1 else 0 end) as [Tot Ages 11-12]
,sum(CASE when AgeCurrent >= 13 and AgeCurrent <= 17 then 1 else 0 end) as [Tot Ages 13-17]
,SUM(CASE when AgeCurrent >= 18 and AgeCurrent <= 20 then 1 else 0 end) as [Tot Ages 18-20]
,sum(CASE when AgeCurrent >= 21 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 21-24]
from (select distinct agencyid, activityID, regid, agecurrent from CTE_Hours) d
Where d.AgencyID = h.AgencyID And d.ActivityId = h.ActivityId
group by agencyID, ActivityID) I
group by H.Agency, A.ActivityName
Order by H.Agency, A.ActivityName
```

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial