How to change the breakout based on grouping

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
 

Open in new window

al4629740Asked:
Who is Participating?
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Basically, the SUM above is counting the records and not SUMming the values, fixed an issues with the existing code and try whether cnt column matches your requirement or not..
;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
 

Open in new window

0
al4629740Author Commented:
No that doesn't do it.  The output had many rows.  The output should be one row providing the totals
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you please provide some sample records to understand your result set and expectation better..
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Mark WillsTopic AdvisorCommented:
So, like a total line ?

For all the SUM columns ? and MAX ? Any others ?
0
Mark WillsTopic AdvisorCommented:
Noticed the A.ActivityName = 'Community Committee'

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
 

Open in new window

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
Mark WillsTopic AdvisorCommented:
Hi al4629740,

How are you going with this ?

Is there more you need ?
0
al4629740Author Commented:
Sorry for not responding as I had some personal issues preventing me.  

Thanks Mark.  This seems to be the solution as you provided.
0
Mark WillsTopic AdvisorCommented:
No Worries. Hope your issues can also achieve a Good Answer :)

And always family first - you need to take care of personal matters first and foremost.

Kind Regards,
Mark Wills
0
al4629740Author Commented:
Thanks Mark.  You are so right.
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
Query Syntax

From novice to tech pro — start learning today.