BigQuery SQL to count the number of UNIQUE matching values

Dataset:

    | Host      | Risk           | Name      |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | High           | ValueB    |
    | 10.1.1.1  | High           | ValueB    |
    | 10.1.1.1  | High           | ValueB    |
    | 10.1.1.1  | Critical       | ValueC    |
    | 10.1.1.1  | Critical       | ValueC    |
    | 10.1.1.1  | Critical       | ValueC    |
    | 10.1.1.1  | Critical       | ValueC    |

Expected Result:

    | Host      | Critical     | High      |
    | 10.1.1.1  | 2            | 1         |

Solution:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '10.1.1.1' Host, 'Critical' Risk, 'ValueA' Name UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL
  SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL
  SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' 
)
SELECT 
  Host, 
  COUNT(DISTINCT IF(Risk='Critical', Name, NULL)) Critical,
  COUNT(DISTINCT IF(Risk='High', Name, NULL)) High
FROM `project.dataset.table`
GROUP BY Host   

Result

Row Host        Critical    High     
1   10.1.1.1    2           1    

Mike-Barn

posted on 01 Feb 21