I have

and I want

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?

CodePudding user response：

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.