What is meaning of SQL error "Each function argument is an expression, not a query"

Question

I have a table customer_purchases with 3 columns: customer_id, product_id, quantity, where each customer_id can buy multiple products having different quantities.

I need to find customers whose maximum quantity bought (among all the products purchased) is greater than average quantity of every/each customer. I am using Big Query.

I wrote the following query :

SELECT customer_id 
FROM customer_purchases
GROUP BY customer_id
HAVING MAX(quantity) > MAX(SELECT AVG(quantity) 
                           FROM `farmers_market.customer_purchases`
                           GROUP BY customer_id)
ORDER BY customer_id

But I get this error:

Each function argument is an expression, not a query; to use a query as an expression, the query must be wrapped with additional parentheses to make it a scalar subquery expression

What does this mean, and how do I resolve this problem?

Answer 1

You want something more like this:

SELECT p.customer_id 
FROM customer_purchases p
group by p.customer_id
having sum(p.quantity) > (select avg( (select sum(p0.quantity) 
                        from `customer_purchases` p0
                        group by p0.customer_id) ) )
order by customer_id

Note the extra set of parentheses inside the avg() function. Also noticed that I changed which aggregate functions you were using, since the original functions were not correct for the desired results.

Answer 2

1. Please change the below query to the below format

SELECT customer_id 
FROM customer_purchases
GROUP BY customer_id
HAVING MAX(quantity) > MAX((SELECT AVG(quantity) 
                           FROM `farmers_market.customer_purchases`
                           GROUP BY customer_id))
ORDER BY customer_id

2. After changing if you are getting the error "sql Scalar subquery produced more than one element" then please run the below query

SELECT customer_id 
FROM customer_purchases
GROUP BY customer_id
HAVING MAX(quantity) > MAX((SELECT AVG(quantity) 
                           FROM `farmers_market.customer_purchases`
                           ))
ORDER BY customer_id

When you are finding the average and using the group by, then it's finding average quantity for each customer id. After this the max() is happening again for each customer id, which is returning multiple results to the outer query for comparison. As per definition, Scalar subqueries cannot produce more than a single row. This is the reason I have removed the group by and its going to show the result.