in a BigQuery I have a table.
| VM | Package |
|---|---|
| VM1 | A |
| VM2 | B |
| VM3 | A |
| VM4 | B |
| VM1 | B |
| VM2 | C |
| VM3 | B |
| VM4 | C |
How can I get results, so all distinct VMs would be listed, but having Package column with value null (or empty, or Yes and No) if particular package not exists. I.e. I need to be listed all VMs (without duplicating), which have the package A installed, and the rest with value let say null:
| VM | Package |
|---|---|
| VM1 | A |
| VM2 | null |
| VM3 | A |
| VM4 | null |
You should check COUNT of package A for each VM and apply condition on the COUNT
SELECT VM, IF(COUNTIF(Package = 'A') = 0, NULL, 'A') AS Package
FROM table1
GROUP BY VM