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
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