Scalar subquery produced more than one element - bigquery

user_5214960 0
Viewed 45

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?

google-bigquery
1 Answers

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

enter image description here

user_52219440
Related