CODEWITHSUNDEEP
sqlsql-servercount
gt.guybrush
gt.guybrush

Reputation: 1378

sql, conditional count distinct

i have to do a difficult filter in a count distinct.

having data likt that

auditor - anomaly
Bob - s
Bob - n
Bob - n
Jon - n
Jon - n
Rob - s
Rob - n

i have to count the number of person that have only 's'

this count is part of a complex query so iam looking for a way to do it inline in the select statement:

SELECT
    SELECT      
            Office
        ,   SUM(OvverideRating)  AS [n. override]                   
        ,   COUNT(DISTINCT inspectorWithOverride) AS [n. auditor]
        ,   CAST(SUM(NonMotivato) AS FLOAT) / CAST(SUM(OvverideRating) AS FLOAT) [% on override]
        ,   COUNT(DISTINCT anomaly) AS [n. auditor with anomaly]
        ,   CAST(COUNT(DISTINCT anomaly) AS FLOAT) / CAST(COUNT(DISTINCT IspettoreConOverride) AS FLOAT) AS [% auditor with anomaly]    

FROM 
        (
        SELECT  DISTINCT
                Office  
            ,   activityID
            ,   CASE
                    WHEN [Override] ='S' THEN 1
                    ELSE 0
                END     AS OvverideRating
            ,   CASE
                    WHEN CAST(memCp_NoteTeamLeader AS NVARCHAR(MAX)) is not null AND [Override] ='S' THEN 1
                    ELSE 0
                END  AS Motivated
           ,    CASE
                    WHEN CAST(memCp_NoteTeamLeader AS NVARCHAR(MAX)) is null AND [Override] ='S' THEN 1
                    ELSE 0
                END AS NotMotivated
            ,   CASE
                    WHEN [Override] ='S' THEN strCb_IDIspettore
                    ELSE NULL
                END AS inspectorWithOverride
            ,   CASE
                    WHEN CAST(memCp_NoteTeamLeader AS NVARCHAR(MAX)) is null AND [Override] ='S' THEN strCb_IDIspettore
                    ELSE NULL
                END AS anomaly

        FROM ... complex subquery ...
        ) AS data
    GROUP BY 
        Office

Upvotes: 0

Views: 144

Answers (1)

gt.guybrush
gt.guybrush

Reputation: 1378

I done it in this way I count total distinct auditor minus the count of those that have an "N"

Upvotes: 1

Related Questions