How can I get several different unduplicated counts in the same SQL query

I have an attendance table in SQL Server 2012 named tblOrgHours. (attached)  This is where I log all my attendance for different agencies in tracking how many hours each activity has been performed each day by different kids.  The ActivityID in this table is relational with tblOrgActivity. (attached)  The RegID in that same table is relational with the student information found in table tblOrgRegistrations. (attached)  I need to create an sql query that will output unduplicated counts of the kids who had greater than 0 hours of service during a specified period of time.  The unduplicated demographic counts are broken out as you see in the attached output file.  

After looking at my output, how is is possible to get all this information pulled up from one sql query?
tblOrgHours.xlsx
tblOrgActivities.xlsx
tblOrgRegistrations.xlsx
Output-Example.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.

Scott PletcherSenior DBACommented:
If you could post usable sample data -- CREATE TABLE and INSERT statement(s) -- I don't think the query will be that hard to write.
0
PortletPaulEE Topic AdvisorCommented:
Several spreadsheets have been provided and it seems the following would form joins:

tblOrgHours.ActivityID = tblOrgActivity.ActivityID
tblOrgHours.RegID = tblOrgRegistrations.RegID

tblOrgHours
HourID	Agency	AgencyID	Program	ActivityID	RegID	ActivityDate	Hours	HourTimeFrom	HourTimeTo	SubGroup	ParticipantHour	VolunteerHour	Area	ActivityDetail	Fiscal	EntryTime
275922	Administrator	74	CSW	6	6535	2016-06-30	1	09:00:00	10:00:00		1	0	NULL	NULL	2018	2017-07-04
275923	Administrator	74	CSW	6	6536	2016-06-30	1	09:00:00	10:00:00		1	0	NULL	NULL	2018	2017-07-04

Open in new window

tblOrgActivity
ActivityID	Agency	AgencyID	ObjectiveID	ActivityName	PrePostTest	Manual	ActivityDesc	ActivityType	Location	TimeFrom	TimeTo	DateFrom	DateTo	AgeFrom	AgeTo	Volunteers	VolunteersHow	Mon	Tues	Wed	Thurs	Fri	Sat	Sun	NA	0-7	12-Aug	13-17	18-20	21-25	26+	EntryTime	Fiscal	Data
543	Administrator	74	444	Community Committee	0	1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	0	0	0	0	0	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	00:00.0	2018	NULL
544	Administrator	74	444	Youth Committee	0	1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	0	0	0	0	0	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	00:00.0	2018	NULL

Open in new window

tblOrgRegistrations
RegID	Agency	AgencyID	RegDate	Inactive	LastName	FirstName	NickName	BirthDate	AgeRegistration	AgeCurrent	Participant	Volunteer	Board	YouthCommittee	Sector	Gender	Address	City	Zip	Phone	Email	Race	Language	Parent	EmergencyContact	EmerRelationship	EmerPhone	EnrolledSchool	School	Grade	Referral	Service	CommunityArea	EntryTime	Fiscal	SubGroup	AgeReg	Preparer	BackgroundInfo	StudentID
6535	Administrator	74	2017-07-04	0	FakeLast	FakeFirst	We	2000-07-04	17	17	1	1	0	NULL		Male	12	21	23	23	sd	Asian	English	21	21	21	() -21	Yes	21	K	Self	Cultural	Archer Heights	40:51.0	2018		16	21	21	NULL
6536	Administrator	74	2017-07-04	0	Sdfa	Sdaf	Asd	2001-07-04	16	16	1	0	0	NULL		Male	12	12	12	23	12	Asian	Spanish	23	2323	23	() -23	No	23	7	Family	Educational	West Garfield Park	41:44.0	2018		15	23	23	NULL
6561	Administrator	74	2017-09-18	0	Sam	Sam	D	2005-09-18	12	12	1	1	1	1	Faith Based	Male	32	234	324	32	23	African-American	Spanish	34	324	324	() -234	Yes	324	K	Self	Cultural	Armour Square	18:11.0	2018		11	234	234	NULL
6562	Administrator	74	2017-09-18	0	Sam	Sam	231	2006-09-18	11	11	1	1	0	1		Male	231	234	234	324	23	African-American	English	324	234	342	() -423	No	234	K	Court	Cultural	Archer Heights	20:50.0	2018		10	324	234	NULL

Open in new window

PARTICIPANTS - RACE                                                                                         (unduplicated count)	7/1/17 to 12/1/17
Total Number of Participants:  Asian	0
Total Number of Participants:  African-American 	0
Total Number of Participants:  Native-American	0
Total Number of Participants: Caucasian	0
Total Number of Participants:  Multi-Racial                      	0
Total Number of Participants: Hispanic/Latino 	0
PARTICIPANTS - AGE                                                                                                    (unduplicated count)	1st Quarter
Total Number of Participants: 11-13 years of age         	0
Total Number of Participants: 14-18 years of age          	0
Total Number of Participants: Young Adults (19-24)      	0
Total Number of Participants: Older Adults (25-65)        	0
 Total Number of Participants: Seniors (66+)      	0
PARTICIPANTS - GENDER                                                                                   (unduplicated count)	1st Quarter
Total Number of  Females                                          	0
Total Number of Males                                             	0
Total Number Participants by Gender	0

Open in new window

1
Mark WillsTopic AdvisorCommented:
Seems very familiar....

Think last time was something like:
With CTE_Hours as
( 
  select distinct AgencyID, Agency, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate
  from tblOrgHours 
  where [Hours] > 0
) 
select  H.Agency
       ,A.ActivityName
       ,H.ActivityDate
       ,Count(H.RegID) as [# individuals]
       ,SUM(CASE when R.AgeCurrent < 14 then 1 else 0 end) as [Ages 11-13]
       ,SUM(CASE when R.AgeCurrent > 13 then 1 else 0 end) as [Ages 14-18]
       ,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]

from CTE_Hours H
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID

where R.AgeCurrent between 11 and 18

group by H.Agency,A.ActivityName,H.ActivityDate 
Order by 1,2,3

Open in new window

How is it different this time ? is it  not being concerned about Agency (et al) and just getting counts per regid ?

It would be a similar approach (probably no need for CTE this time)

Possibly more like :
select  H.Agency
       ,A.ActivityName
       ,cast(H.ActivityDate as Date) ActivityDate
       ,Count(H.RegID) as [# individuals]
       ,SUM(CASE when R.AgeCurrent < 14 then 1 else 0 end) as [Ages 11-13]
       ,SUM(CASE when R.AgeCurrent > 13 then 1 else 0 end) as [Ages 14-18]
       ,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]

from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID

where H.[Hours] > 0
and R.AgeCurrent between 11 and 18

group by H.Agency,A.ActivityName,cast(H.ActivityDate as Date) 
Order by 1,2,3

Open in new window

1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
Exactly Mark,

this request is the same style of need and simply needs a set of case expressions inside the COUNT() function to calculate the wanted numbers for a given date range. Unable to devote more time to this right now.
0
al4629740Author Commented:
Hey Mark,

Here's the difference.  These are unduplicated counts where as the original sql query I had worked with you before had duplicated counts.

It would be unique RegIDs in this case.
0
al4629740Author Commented:
Activities would not be relevant, but the hours would be relevant as they need to be > 0
0
PortletPaulEE Topic AdvisorCommented:
For "distinct" counts instead of this style
SUM(CASE when R.AgeCurrent < 14 then 1 else 0 end) as [Ages 11-13]

use this style
COUNT(DISTINCT CASE when R.AgeCurrent < 14 then REGID else NULL end) as [Ages 11-13]

The COUNT() function ignores nulls but does allow for counting distinct non-null  values.

So each of your wanted calculations could use this style of case expression inside the count function.

The where clause you use would control the date range of the query.
1
al4629740Author Commented:
With CTE_Hours as
(select distinct AgencyID, Agency, Classification, Objectives, Deliverables, Advocate, AdvocacyType, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal from tblOrgHours 
  where [Hours] > 0) select  H.Agency
       ,A.ActivityName
       ,Count(Distinct CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]
       ,Count(Distinct CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]
	   ,Count(Distinct CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]
	   ,Count(Distinct CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]
	   ,Count(Distinct CASE when R.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]
	   ,Count(Distinct CASE when R.Board = 1 then 1 else 0 end) as [CommunityCommittee]
	   ,Count(Distinct CASE when R.YouthCommittee = 1 then 1 else 0 end) as [YouthCommittee]
	   ,Count(Distinct CASE when R.Parentcheck = 1 then 1 else 0 end) as [Parentcheck]
	   ,Count(Distinct CASE when R.CommunityResident = 1 then 1 else 0 end) as [CommunityResident]
	   ,Count(Distinct CASE when R.Race = 'Asian' then 1 else 0 end) as [Asian]
	   ,Count(Distinct CASE when R.Race = 'African-American' then 1 else 0 end) as [African-American]
	   ,Count(Distinct CASE when R.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
	   ,Count(Distinct CASE when R.Race = 'Native-American' then 1 else 0 end) as [Native-American]
	   ,Count(Distinct CASE when R.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
	   ,Count(Distinct CASE when R.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
       ,Count(Distinct CASE when R.Gender = 'Male' then 1 else 0 end) as [Male]
       ,Count(Distinct CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]


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,A.ActivityName,H.ActivityDate
Order by H.Agency,H.ActivityDate,A.ActivityName
 

Open in new window

0
al4629740Author Commented:
After running the code above, every column shows at least one person, which is erroneous.  

Agency	ActivityName	Ages 11-13	Ages 14-18	Ages 19-24	Ages 25-65	Ages 65+	CommunityCommittee	YouthCommittee	Parentcheck	CommunityResident	Asian	African-American	Caucasian	Native-American	Multi-Racial	Latino-Hispanic	Male	Female
Administrator	New Activity Today	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Administrator	Board Meeting	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Administrator	Board Meeting	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Administrator	Community Committee	2	2	1	1	1	2	2	1	1	2	2	1	1	1	1	2	2
Administrator	Community Committee Meeting	2	2	1	1	1	2	2	1	1	2	2	1	1	1	1	1	1

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
Do NOT use "1" in those case expressions you MUST return tblOrgRegistrations.regid instead
0
al4629740Author Commented:
Sorry, I'm not following.  Could you demo one of the lines?
0
Mark WillsTopic AdvisorCommented:
OK, if we have regid = 123 on  2017-10-11
and if we have regid = 123 on  2017-10-12

does that count as 1 regid for the period  2017-01-01 to 2017-12-31

I think things like
where ...
And h.Agency = 'Administrator'
And H.Fiscal = 2018
And H.ActivityDate >= '1/1/17'
And H.ActivityDate < '12/31/17'

needs to be at the top.... with hours > 0

we only want where R.whatever after the counts
0
Mark WillsTopic AdvisorCommented:
assuming the answer to regid 123 was 1 for the period, Does this go close ?
With CTE_Hours as
(
 select distinct  RegID --, cast(ActivityDate as Date) ActivityDate --, Fiscal 
 from tblOrgHours h 
 inner join tblOrgActivities A on H.ActivityID = A.ActivityID
 where [Hours] > 0 
 And h.Agency = 'Administrator'
 And H.Fiscal = 2018
 And H.ActivityDate >= '20170101'
 And H.ActivityDate < '20180101'
)  
  select  count( h.regid) as regid
       ,Count(DISTINCT CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]
       ,Count(DISTINCT CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]
	   ,Count(DISTINCT CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]
	   ,Count(DISTINCT CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]
	   ,Count(DISTINCT CASE when R.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]
	   ,Count(DISTINCT CASE when R.Board = 1 then 1 else 0 end) as [CommunityCommittee]
	   ,Count(DISTINCT CASE when R.YouthCommittee = 1 then 1 else 0 end) as [YouthCommittee]
	   ,Count(DISTINCT CASE when R.Parentcheck = 1 then 1 else 0 end) as [Parentcheck]
	   ,Count(DISTINCT CASE when R.CommunityResident = 1 then 1 else 0 end) as [CommunityResident]
	   ,Count(DISTINCT CASE when R.Race = 'Asian' then 1 else 0 end) as [Asian]
	   ,Count(DISTINCT CASE when R.Race = 'African-American' then 1 else 0 end) as [African-American]
	   ,Count(DISTINCT CASE when R.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
	   ,Count(DISTINCT CASE when R.Race = 'Native-American' then 1 else 0 end) as [Native-American]
	   ,Count(DISTINCT CASE when R.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
	   ,Count(DISTINCT CASE when R.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
       ,Count(distinct CASE when R.Gender = 'Male' then 1 else 0 end) as [Male]
       ,Count(distinct CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]

from CTE_Hours H                         
inner join tblOrgRegistrations R on H.Regid = R.RegID

where R.AgeCurrent between 11 and 999

Open in new window

0
al4629740Author Commented:
Yes that would count as only one for that period
0
al4629740Author Commented:
Let me evaluate the code you sent and see
0
PortletPaulEE Topic AdvisorCommented:
If a case expression ONLY returns one or zero  then a DISTINCT COUNT of that case expression can ONLY be 0 or 1 or 2

select  count( h.regid) as regid
       ,Count(DISTINCT CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then h.regid  else NULL end) as [Ages 11-13]
   
You MUST return the regid value itself (not 1)
1
Mark WillsTopic AdvisorCommented:
Yep, Paul is absolutely correct....

With CTE_Hours as
(
 select distinct  RegID --, cast(ActivityDate as Date) ActivityDate --, Fiscal 
 from tblOrgHours h 
 inner join tblOrgActivities A on H.ActivityID = A.ActivityID
 where [Hours] > 0 
 And h.Agency = 'Administrator'
 And H.Fiscal = 2018
 And H.ActivityDate >= '20140101'
 And H.ActivityDate < '20180101'
)  
  select  count( h.regid) as [number of people]
       ,Count(DISTINCT CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then h.regid else null end) as [Ages 11-13]
       ,Count(DISTINCT CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then h.regid else null end) as [Ages 14-18]
       ,Count(DISTINCT CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then h.regid else null end) as [Ages 19-24]
       ,Count(DISTINCT CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then h.regid else null end) as [Ages 25-65]
       ,Count(DISTINCT CASE when R.AgeCurrent >= 66 then h.regid else null end) as [Ages 65+]
       ,Count(DISTINCT CASE when R.Board = 1 then h.regid else null end) as [CommunityCommittee]
       ,Count(DISTINCT CASE when R.YouthCommittee = 1 then h.regid else null end) as [YouthCommittee]
       ,Count(DISTINCT CASE when R.Parentcheck = 1 then h.regid else null end) as [Parentcheck]
       ,Count(DISTINCT CASE when R.CommunityResident = 1 then h.regid else null end) as [CommunityResident]
       ,Count(DISTINCT CASE when R.Race = 'Asian' then h.regid else null end) as [Asian]
       ,Count(DISTINCT CASE when R.Race = 'African-American' then h.regid else null end) as [African-American]
       ,Count(DISTINCT CASE when R.Race = 'Caucasian' then h.regid else null end) as [Caucasian]
       ,Count(DISTINCT CASE when R.Race = 'Native-American' then h.regid else null end) as [Native-American]
       ,Count(DISTINCT CASE when R.Race = 'Multi-Racial' then h.regid else null end) as [Multi-Racial]
       ,Count(DISTINCT CASE when R.Race = 'Latino-Hispanic' then h.regid else null end) as [Latino-Hispanic]
       ,Count(distinct CASE when R.Gender = 'Male' then h.regid else null end) as [Male]
       ,Count(distinct CASE when R.Gender = 'Female' then h.regid else null end) as [Female]

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

where R.AgeCurrent between 11 and 999

Open in new window



to do the 1 else 0 we need to SUM

e.g.
       ,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.Gender = 'Male' then 1 else 0 end) as [Male]
       ,sum(CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]

Open in new window


Thanks again PortletPaul for pointing it out
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:
It sure as heck looks like its working right.  I checked individually and found the numbers were reconciling.
0
al4629740Author Commented:
So that I understand this, it appears both the scenarios presented in the accepted answer above work.  We can't use Distinct count because it returned the erroneous value (for a reason that I'm still not clear) so hence we had to use regid since it is a value of 1 in of itself.   OR we could have used the SUM function without the distinct CASE since distinct RegID is used in the previous expression CTE_Hours to achieve the same results.
0
Mark WillsTopic AdvisorCommented:
well the DISTINCT values of the COUNT of  "then 1 else 0" can be 0 or 1 or both (ie 2)

where as the SUM will actually add up all those 1's

For the COUNT to work, we need different values for it to actually count, ie the regid


think of COUNT distinct regid 123, 456, 789, 111, 222, 333  there are 6 distinct values
then think of COUNT distinct 1,1,1,0,0,0  there is only a 0 and a 1 = 2 distinct values

conversely

think of SUM 1,1,1,1,0,0,1  = 5
and I dont want to think of SUM 123, 456, 789  :)
0
PortletPaulEE Topic AdvisorCommented:
>>"We can't use Distinct count "
That statement is incorrect, you DO use COUNT(DISTINCT ...) to get the correct answers

The problem is in how the case expressions are formed, not the count function.

This is the correct case expression style: CASE when [condition(s)] then h.regid else null end

This is an incorrect case expression style: CASE when [condition(s)] then 1 else 0 end

test_table
HourID      RegID   then_1
275922      6535               1
275923      6536               1

select
     count(distinct Regid)  as regid_count
   , count(distinct then_1)  as then_1_count
from test_table

regid_count   then_1_count
                   2                         1
1
al4629740Author Commented:
Now I get it!  Great back to back explanations on count distinct
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
SQL

From novice to tech pro — start learning today.