BigQuery SQL to count the number of UNIQUE matching values
Dataset:
\n\n | Host | Risk | Name |\n | 10.1.1.1 | Critical | ValueA |\n | 10.1.1.1 | Critical | ValueA |\n | 10.1.1.1 | Critical | ValueA |\n | 10.1.1.1 | Critical | ValueA |\n | 10.1.1.1 | Critical | ValueA |\n | 10.1.1.1 | Critical | ValueA |\n | 10.1.1.1 | High | ValueB |\n | 10.1.1.1 | High | ValueB |\n | 10.1.1.1 | High | ValueB |\n | 10.1.1.1 | Critical | ValueC |\n | 10.1.1.1 | Critical | ValueC |\n | 10.1.1.1 | Critical | ValueC |\n | 10.1.1.1 | Critical | ValueC |\nExpected Result:
\n\n | Host | Critical | High |\n | 10.1.1.1 | 2 | 1 |\nSolution:
#standardSQL\nWITH `project.dataset.table` AS (\n SELECT '10.1.1.1' Host, 'Critical' Risk, 'ValueA' Name UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL\n SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL\n SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL\n SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL\n SELECT '10.1.1.1', 'Critical', 'ValueC' \n)\nSELECT \n Host, \n COUNT(DISTINCT IF(Risk='Critical', Name, NULL)) Critical,\n COUNT(DISTINCT IF(Risk='High', Name, NULL)) High\nFROM `project.dataset.table`\nGROUP BY Host \nResult
\n\nRow Host Critical High \n1 10.1.1.1 2 1 \n
Mike-Barn
posted onEnjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment