Scalar subquery produced more than one element - bigquery

Question

I have 2 tables 1 that records searches done by users and 1 for product categories

I'd like to retrieve the most common searches keywords and see if it matches current categories and how often categories appear in searches

Table 1 (searches)

search_id	search_name
1	iPhone 6 with case
2	desks for big monitor
3	laptop case walmart
4	laptop case
5	iPhone 6
6	laptop monitor
7	iPhone 7 with case
8	iPhone 6
8	tv monitor

Table 2 (categories)

category_id	category_name
1	iPhone
2	monitor
3	laptop

WITH AS serach_results
(
Select search_name, count(*) from searches
 group by search_name
  having count(*) >=3
).

select count (category_name), category_name
    from categories
    where regexp_contains(category_name), (select search_name from serach_results))

I expect something like this

f_	category_name
4	iPhone
2	monitor
4	laptop

I get this error Scalar subquery produced more than one element I understand the BQ logic behind this error but how can I get the expected result by BQ?

Answer 1

Use below approach

with search_results as (
  Select search_name, count(*) cnt from searches
  group by search_name
  -- having count(*) >=3
)
select sum(if(regexp_contains(search_name, category_name), cnt, 0)), category_name
from search_results, categories
group by category_name             

if applied to sample data in your question - output is