Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I have the following SQL code attached. Everything works well with this code but there are a few additional parameters that I need in the output. The current output lists each activity by rows. Within those rows I need a column that show how many of that particular type of activity there is and how many unduplicated individuals that attended for that period. For example

ActivityName #of times #unduplicated #of individuals (duplicated) ActivityDate

Basketball 3 10 3 1/1/18

Basketball 3 10 10 1/2/18

Basketball 3 10 9 1/3/18

Volleyball 2 8 7 1/1/18

Volleyball 2 8 6 1/2/18

Hopefully this makes sense. You will notice in the desired output example above that the unduplicated number is repeated for one activity to show how many there were. The unduplicated column\, already being calculated, shows how many attended that specific date.

tblOrgActivities--1-.xlsx

tblOrgHours.xlsx

ActivityName #of times #unduplicated #of individuals (duplicated) ActivityDate

Basketball 3 10 3 1/1/18

Basketball 3 10 10 1/2/18

Basketball 3 10 9 1/3/18

Volleyball 2 8 7 1/1/18

Volleyball 2 8 6 1/2/18

Hopefully this makes sense. You will notice in the desired output example above that the unduplicated number is repeated for one activity to show how many there were. The unduplicated column\, already being calculated, shows how many attended that specific date.

```
With CTE_Hours as
(select distinct AgencyID, Agency, Classification, Objectives, Advocate, AdvocacyType, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal from tblOrgHours
where [Hours] > 0) select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Advocate
,H.AdvocacyType
,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
where R.AgeCurrent between 11 and 999
And h.Agency = 'Administrator'
And H.Fiscal = 2018
And H.ActivityDate >= '1/1/17'
And H.ActivityDate < '12/31/17'
group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3
```

tblOrgRegistrations--1-.xlsxtblOrgActivities--1-.xlsx

tblOrgHours.xlsx

I don't understand what you mean by duplicate and unduplicated counts. I write a sample query to show for All agencies how many basketball and how many volleyball are played and when they were played:

create table #tblOrgHours (

AgencyID INT

, ActivityName Varchar(200)

, ActivityDate Date

)

INSERT INTO #tblOrgHours Values

(1,'Basketball','2017-01-0

,(1,'Basketball','2017-01-

,(1,'Basketball','2017-01-

,(1,'Basketball','2017-01-

,(3,'Basketball','2017-01-

,(3,'Basketball','2017-02-

,(3,'Basketball','2017-03-

,(1,'Valleyball','2017-04-

,(1,'Valleyball','2017-04-

,(1,'Valleyball','2017-04-

,(1,'Valleyball','2017-07-

,(2,'Valleyball','2017-07-

,(2,'Valleyball','2017-07-

;With CTE_Hours as

(select distinct

AgencyID

, ActivityName

, ActivityDate

-- , ROW_NUMBER() OVER (PARTITION BY AgencyID,ActivityName ORDER BY ActivityDate ASC) rn

from #tblOrgHours

where 1=1)

, ActivityName_Count as

(select

ActivityName

, Count(cast(AgencyID As Varchar(30)) + cast(ActivityDate As Varchar(30))) As Count_ActivityDate

from #tblOrgHours

where 1=1

group by ActivityName

)

-- This will display for ALL agencies how many basketball (and valley ball) are played and on which date they were played.

select CTE_Hours.ActivityName

,CTE_Hours.ActivityDate

,ActivityName_Count.Count_

-- ,rn

from CTE_Hours

inner join ActivityName_Count

on ActivityName_Count.Activit

where 1=1

drop table #tblOrgHours

- Xiao

Thanks for your answer. However, the code I pasted earlier is the code that needs to be modified for this purpose if possible. The example output I was stating was what I needed added onto the current output.

To clarify....

The current query column for #individuals : Count(H.RegID) as [# individuals] remains as is...

Add a new column to reflect the different people for the activity (for the current "where" clause)

The "duplicated" annotation means that the column will show the same value for the activity for as many rows as exists for that activity - I think that part of the requirement is a bit confusing.

I have yet to load your full definitions, but you can 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 [Hours] > 0
And Agency = 'Administrator'
And Fiscal = 2018
And ActivityDate >= '20170101'
And 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]
,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 Agency,Classification,Objectives,Advocate,AdvocacyType,ActivityID, count(Regid) as [Tot Individuals for Activity] from CTE_Hours group by Agency,Classification,Objectives,Advocate,AdvocacyType,Activityid) I on I.Agency = H.Agency and I.Activityid = A.ActivityId and I.Classification = H.Classification and I.Objectives = H.Objectives and I.Advocate = H.Advocate and I.AdvocacyType = H.AdvocacyType
group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3
```

Notice how I moved the WHERE clauses all the way up to CTE_Hours - always best to limit the criteria firstOnce I have loaded the spreadsheets, and tested, I will get back to you with any other changes (if any).

Cheers,

Mark Wills

P.S. I dont think we need to be so pedantic with the I subquery - we might only need AgencyID and ActivityID - but will test and get back to you

A few updates needed to the spreadsheets....

1) Spreadsheets do not match table definitions used in the query - tblorghours and tblorgregistrations

2) Data doesnt match - activityID in tblorghours doesnt have tblactivity entries

Had to do a bit of fiddling, and in looking at the data, think we only need ActivityID and AgencyID for our 'I' subquery

And probably the reason why some activities didnt show (the inner join on the 'I' subquery)...

So, please have a look at :

```
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]
,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(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
```

If the new column is nearly there, but is counting duplicated regid's, then try : count(Distinct Regid)

Or,

Update the spreadsheets (correct columns and matching data) and show some corresponding results

create table #tblOrgHours (

AgencyID INT

, ActivityName Varchar(200)

, ActivityDate Date

)

INSERT INTO #tblOrgHours Values

(1,'Basketball','2017-01-0

,(1,'Basketball','2017-01-

,(1,'Basketball','2017-01-

,(1,'Basketball','2017-01-

,(1,'Basketball','2017-01-

,(3,'Basketball','2017-01-

,(3,'Basketball','2017-02-

,(3,'Basketball','2017-03-

,(1,'Volleyball','2017-04-

,(1,'Volleyball','2017-04-

,(1,'Volleyball','2017-04-

,(1,'Volleyball','2017-07-

,(1,'Volleyball','2017-04-

,(1,'Volleyball','2017-07-

,(2,'Volleyball','2017-07-

,(2,'Volleyball','2017-07-

;With CTE_Hours as

(select distinct

AgencyID

, ActivityName

, ActivityDate

-- , ROW_NUMBER() OVER (PARTITION BY AgencyID,ActivityName ORDER BY ActivityDate ASC) rn

from #tblOrgHours

where 1=1)

, ActivityName_Count as

(select

ActivityName

, Count(cast(AgencyID As Varchar(30)) + cast(ActivityDate As Varchar(30))) As Count_ActivityDate

from #tblOrgHours

where 1=1

group by ActivityName

)

, ActivityName_UnDup_Count as

(select

ActivityName

, Count(distinct cast(AgencyID As Varchar(30)) + cast(ActivityDate As Varchar(30))) As Count_UnDup_ActivityDate

from #tblOrgHours

where 1=1

group by ActivityName

)

-- This will display for ALL agencies how many basketball (and valley ball) are played and on which date they were played.

select distinct

CTE_Hours.ActivityName

,CTE_Hours.ActivityDate

,ActivityName_Count.Count_

,Count_UnDup_ActivityDate As 'Undup No. of Play'

,ActivityName_Count.Count_

-- ,rn

from CTE_Hours

inner join ActivityName_Count

on ActivityName_Count.Activit

inner join ActivityName_UnDup_Count

on ActivityName_UnDup_Count.A

where 1=1

drop table #tblOrgHours

- Xiao

Truthfully, I now need an unduplicated column also for regid and call it [Unduplicated Count]

tblOrgHours.xlsx

Output-from-code-from-Mark.xlsx

After looking at the code you sent, it works and I understood the results incorrectly. The [Tot Individuals for Activity] is an UNDUPLICATED or distinct count for all associated [ActivityID] and that is what I was looking for. Also the [# individuals] is counting all the regids correctly for the full count.

is it possible to also count all distinct [ActivityName] the same way you did with the column [Tot Individuals for Activity]?

I'm trying to do it but line 18 is a bit over my head

And are you asking a new Question :)

Should be able to get that [ActivityName] counter a bit easier using a window function OVER()

```
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [Tot Activities for Agency]
```

Because all the elements for that are already in the Group ByAnd thanks for the spreadsheets.

Cheers,

Mark Wills

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.

Or, are you asking for a count of total regid's for an activity ?

Open in new window

Any chance of new spreadsheets with example results ? Otherwise, we just keep guessing at what column and what duplicated means.... And please use column names in your comments - help narrow down which columns are problematic and why...