sqlgoogle-cloud-platformgoogle-bigquerysubqueryfunction-call

How to return the COUNT of two different parameters?


I'm trying to return how many 'males' or 'females' are counted in each specific event for the products brand name column.

I tried to use subqueries and use the same syntax for both genders. Since I didn't get the results I needed, I tried to count males in one column then subtract the number of the total to get the count of females. I used this code and got the message:

Syntax error: Function call cannot be applied to this expression. Function calls require a path, e.g. a.b.c() at [8:5]

SELECT 
    products_brand_name,
    COUNT(products_brand_name) AS total_males
FROM 
    `bigquery-public-data.fda_food.food_events`
WHERE
    consumer_gender = "Male"
    (
    SELECT
    COUNT((products_brand_name) - (total_males))
    FROM 
      `bigquery-public-data.fda_food.food_events`
    WHERE
      consumer_gender = "Female"
    )
GROUP BY
    products_brand_name
LIMIT 10

Solution

  • Use below approach

    SELECT products_brand_name,
        COUNTIF(consumer_gender = "Male") AS total_males,
        COUNTIF(consumer_gender = "Female") AS total_females,
    FROM `bigquery-public-data.fda_food.food_events`
    GROUP BY products_brand_name
    ORDER BY COUNT(*) DESC
    LIMIT 10
    

    with output

    enter image description here