TSQL : Count distinct values overall and based on criteria by group

user_12479202 0
Viewed 38

I have

Groupvar Subvar Val
G1 A x
G1 A x
G1 B x
G1 B y
G1 C z
G1 C z
G2 A x
G2 A x
G2 B y
G2 B z
G2 B w
G2 C z

and I want

Groupvar All
count (distinct Val)		 A
count(distinct Val
where Subvar=A)		 B
count(distinct Val
where Subvar=B)		 C
count(distinct Val
where Subvar=C)
G1 3 1 2 1
G2 4 1 3 1

Pseudo-code for this could be

    Select 
      Groupvar,
      count(distinct x) as All, 
      count( distinct x where Subvar='A') as A, 
      count( distinct x where Subvar='B') as B, 
      count( distinct x where Subvar='C') as C
    Group by Groupvar

I can do this with


    CREATE TABLE #have (
    Groupvar VARCHAR(2),
    Subvar   VARCHAR(1),
    Val VARCHAR(1));
      
    INSERT INTO #have (Groupvar, Subvar, Val )
    VALUES 
        ('G1', 'A', 'x'),   ('G1', 'A', 'x'),   ('G1', 'B', 'x'),   ('G1', 'B', 'y'),   ('G1', 'C', 'z'),   ('G1', 'C', 'z'),
        ('G2', 'A', 'x'),   ('G2', 'A', 'x'),   ('G2', 'B', 'y'),   ('G2', 'B', 'z'),   ('G2', 'B', 'w'),   ('G2', 'C', 'z');
     
    WITH t1 AS (
      SELECT Groupvar, 'All' AS Subvar, COUNT(DISTINCT Val ) AS N_Val FROM #have T1
      GROUP BY Groupvar
      UNION
      SELECT Groupvar, Subvar, COUNT(DISTINCT Val ) AS N_Val FROM #have T1
      GROUP by Groupvar, Subvar
      )
    SELECT * FROM  t1
    PIVOT (SUM(N_Val) FOR Subvar IN([All],[A],[B],[C])) AS pt

but I wondered if there was a way of doing it in a single select statement that looked more like my pseudo-code exampe?

conditional-aggregation case group-by tsql sql-server
1 Answers

You need CASE expressions inside the COUNT() aggregate function to apply conditional aggregation:

SELECT Groupvar,
       COUNT(DISTINCT Val) [All], 
       COUNT(DISTINCT CASE WHEN Subvar = 'A' THEN Val END) A, 
       COUNT(DISTINCT CASE WHEN Subvar = 'B' THEN Val END) B, 
       COUNT(DISTINCT CASE WHEN Subvar = 'C' THEN Val END) C
FROM tablename
GROUP BY Groupvar;

See the demo.

user_104988280
Related