count distinct where 2 different values are true

Hi Experts,

I am trying to do a distinct count where 2 values are true, but I want it combined into  
one row.  

Here are my results
sessionweek      InstructorUsage  SchCode      LOCNAME                     D2lRole
8/4/2014               113                             185           BROOKWOOD HIGH         Instructor
8/4/2014                   3                             185           BROOKWOOD HIGH         school Admin
8/11/2014         85                             185          BROOKWOOD HIGH          Instructor
8/11/2014          1                             185          BROOKWOOD HIGH        School Admin

This is what I want my results to look like

sessionweek      InstructorUsage  SchCode      LOCNAME                     D2lRole
8/4/2014               116                       185           BROOKWOOD HIGH         Instructor
8/11/2014         86                       185           BROOKWOOD HIGH         Instructor


I want to to combine  the school admin and instructor counts into one.

This is my query

SELECT  CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126) as sessionweek,
       count(distinct UserName) as InstructorUsage,
       e.SchCode,
       LOCNAME,
       D2lRole
FROM [GSDR].[d2l].[D2L_Reporting_D2LSessionExport] as d
     inner join [ProdDatamart].gsdr.d2l.GCPS_OUX_Enrollments_School_tmp as e
       on d.UserName=e.empno
      inner join [ProdDatamart].gsdr.gems.location l
       on e.schCode=l.loc
where SessionStart >= '08-04-2014'
       and D2LRole in ('School Admin', 'Instructor')
       and activesessionduration>0
       and schCode = 185
group by D2LRole,
             CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw,       sessionstart), sessionstart)), 126) ,
             schCode,
             LOCNAME
Butterfly2Asked:
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.

Russell FoxDatabase DeveloperCommented:
If you don't really care about the D2lRole, then you can get rid of it to sum up the rows by date:
SELECT  CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126) as sessionweek,
       count(distinct UserName) as InstructorUsage,
       e.SchCode,
       LOCNAME,
       'Instructor' AS D2lRole
FROM [GSDR].[d2l].[D2L_Reporting_D2LSessionExport] as d
     inner join [ProdDatamart].gsdr.d2l.GCPS_OUX_Enrollments_School_tmp as e 
       on d.UserName=e.empno
      inner join [ProdDatamart].gsdr.gems.location l 
       on e.schCode=l.loc
where SessionStart >= '08-04-2014'
       and D2LRole in ('School Admin', 'Instructor')
       and activesessionduration>0
       and schCode = 185
group by --D2LRole,
             CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw,       sessionstart), sessionstart)), 126) ,
             schCode,
             LOCNAME

Open in new window

0
Butterfly2Author Commented:
Hi Russell,

when I run the above query I get the following results:
sessionweek      InstructorUsage SchCode   LOCNAME                       D2lRole
2014-08-09      113                           185         BROOKWOOD HIGH        Instructor
2014-08-16      88                           185         BROOKWOOD HIGH         Instructor

I need to combine the school admin and instructor counts, this just takes away the school admin counts.
0
PortletPaulfreelancerCommented:
AH! 113 + 3 = 113
but you are counting count(distinct UserName) so if the 3 user names you previously had in 'school admin' are already user names in 'instructor', then the counts would not be added.

Not sure why you got 88 instead of 86 but perhaps there is more data between runs?

Point is, you may have expected a simple addition, but it won't work that way.
0
Ultimate Tool Kit for Technology Solution Provider

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.

Russell FoxDatabase DeveloperCommented:
As ever, @PortletPaul is right: it's probably looking at distinct users, but if you want to sum them separately, you could use the original query as the source and then sum them up at the end. This method uses a CTE:
; WITH CountsByRole
AS (
	SELECT  CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126) as sessionweek,
		   count(distinct UserName) as InstructorUsage,
		   e.SchCode AS SchCode,
		   LOCNAME AS LocName,
		   'Instructor' AS D2lRole
	FROM [GSDR].[d2l].[D2L_Reporting_D2LSessionExport] as d
		 inner join [ProdDatamart].gsdr.d2l.GCPS_OUX_Enrollments_School_tmp as e 
		   on d.UserName=e.empno
		  inner join [ProdDatamart].gsdr.gems.location l 
		   on e.schCode=l.loc
	where SessionStart >= '08-04-2014'
		   and D2LRole in ('School Admin', 'Instructor')
		   and activesessionduration>0
		   and schCode = 185
	group by D2LRole,
		CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126) ,
		schCode,
		LOCNAME
	)

SELECT 
	sessionweek ,
	SUM(InstructorUsage) AS InstructorUsage ,
	SchCode,
	LocName,
	'All roles' AS [Role]
FROM CountsByRole
GROUP BY 
	sessionweek ,
	SchCode,
	LocName

Open in new window

0
Butterfly2Author Commented:
Hi Russell,

I am getting the same results as with the first query you gave me, the thing is I am showing 116 unique Id's in the validation query 113 are instructors and 3 are school admins.  I didn't the ones for 2014-08-16 (2014-08-11) yet.
0
Butterfly2Author Commented:
ok I figured it out. Here is the query that worked for me:

select sessionweek,
       count(*) as InstructorUsage,
       SchCode,
       LOCNAME,
       'instructor' as D2lRole
 from
(SELECT distinct CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126) as sessionweek,
        UserName as InstructorUsage
       --e.SchCode,
       --LOCNAME,
      -- D2lRole
FROM [GSDR].[d2l].[D2L_Reporting_D2LSessionExport]
where D2LRole in ('School Admin', 'Instructor')
       and activesessionduration>0) as d
     inner join [ProdDatamart].gsdr.d2l.GCPS_OUX_Enrollments_School_tmp as e
       on d.InstructorUsage=e.empno
      inner join [ProdDatamart].gsdr.gems.location l
       on e.schCode=l.loc
where sessionweek >= '2014-08-04'
       
       and schCode = 185

 group by sessionweek,
  SchCode,
       LOCNAME
       --D2lRole
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
awking00Commented:
select sessionweek, instructorusage, schcode, locname, d2lRole from
(select sessionweek,
 sum(instructorusage) over (partition by sessionweek order by  sessionweek)  as instructorusage, schcode, locname, d2lRole,
 row_number() over (partition by sessionweek order by upper(d2lrole)) rn
 from yourquery) as x
where x.rn = 1;
0
Butterfly2Author Commented:
none of the other suggestions gave me the correct results.  I just kept playing around with the query until I got my results.
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.

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.