CODEWITHSUNDEEP
sql-server-2008
Toby
Toby

Reputation: 145

SQL Server Count divide count with condition

I have the below table. I would like to count all unique Ids and then divide by a count where Role is Teacher.

Table A

ID    Userid    Role
 1      A        Teacher
 2      b        Teacher
 3      c        Student

Something like below. count(distinct id) / count(distinct id) When Role = Teacher

Upvotes: 0

Views: 1376

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

You can use a simple COUNT DISTINCT with an aggregated CASE expression. I've converted to float so that the result returns decimals but you will want to check out your data types before applying to your system.

Temp Table for sample data

CREATE TABLE #TestData (ID float, Userid nvarchar(1), Role nvarchar(7))
INSERT INTO #TestData
VALUES
(1,'A','Teacher')
,(2,'B','Teacher')
,(3,'C','Student')

Query

SELECT
COUNT(distinct td.ID) DistinctID
,SUM(CASE WHEN td.Role = 'Teacher' THEN 1 ELSE 0 END) Teachers
,CONVERT(float,COUNT(distinct td.ID)) / CONVERT(float,SUM(CASE WHEN td.Role = 'Teacher' THEN 1 ELSE 0 END)) FinalField
FROM #TestData td

Result

DistinctID  Teachers    FinalField
3           2           1.5

Upvotes: 1

Related Questions