I am one Week into BQ now (for my Master Thesis) and after many hours of reading docs I am now hanging at this point:

I use the censys Datasets and want to count the number of hosts with ports open in country 'AT', 'DE', 'CH'. So far I got it working for each country itself. But so save Costs (maybe) I want to count it for all 3 countries at once. Or when I can't save costs with this approach I can stay with my first solution and iterate over it for every country.

Currently working for one Country at a time:

SELECT
    ports,
    count(ports) AS value
FROM
    (
        SELECT
            ip,
            ports
        FROM
            `censys-io.ipv4_public.20171231` i, i.ports
        WHERE
            location.country_code LIKE 'AT'
    )
GROUP BY
    ports

Trying to combine all countries:

SELECT
    location.country_code,
    ports,
    count(ports) OVER (PARTITION BY location.country_code) AS value
FROM
    (
        SELECT
             location.country_code,
             ports 
        FROM
            `censys-io.ipv4_public.20171231` i, i.ports
        WHERE
            location.country_code LIKE 'AT', 'DE', 'CH'
    )
GROUP BY
    ports

It gives me (when I ignore the WHERE error in 6):

Error: Unrecognized name: location at [2:8]

Given the proposed Answer here a screenshot from the error with schema included:

I don't know if this is the right way to do it, or if I should use other functions.

Thanks for your help!