[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

count distinct where 2 different values are true

Posted on 2014-08-20
8
Medium Priority
?
233 Views
Last Modified: 2014-08-25
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
0
Comment
Question by:Butterfly2
8 Comments
 
LVL 14

Expert Comment

by:Russell Fox
ID: 40275206
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
 

Author Comment

by:Butterfly2
ID: 40275213
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40275228
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Russell Fox
ID: 40275243
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
 

Author Comment

by:Butterfly2
ID: 40275245
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
 

Accepted Solution

by:
Butterfly2 earned 0 total points
ID: 40275258
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
 
LVL 32

Expert Comment

by:awking00
ID: 40276271
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
 

Author Closing Comment

by:Butterfly2
ID: 40282611
none of the other suggestions gave me the correct results.  I just kept playing around with the query until I got my results.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question