'BQ query, not exists/joins
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 |
Solution 1:[1]
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
Solution 2:[2]
Another option
select VM,
if('A' in unnest(array_agg(Package)), 'A', null) Package
from your_table
group by VM
with output
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | dikesh |
| Solution 2 | Mikhail Berlyant |