sql error ORA-00934: Group function is not allowed here-CodePudding

When I run the following query, I get ORA-00934: group function is not allowed here

What is the problem?

Select cust_name 
from Customers 
where 
  state = 'California' AND 
  cust_id in(
    select cust_id 
    from Orders 
    where 
      count(cust_id) >= 1 AND 
      book_id in(select book_id from Books where category = 'Computers') 
    group by cust_id
  )

CodePudding user response：

You wrote:

where 
  count(cust_id) >= 1 AND

You cannot use a COUNT, MIN, MAX, AVG or other aggregate function in a WHERE clause because at the time the WHERE is executed the GROUP BY has not yet been done so there is no aggregation. SQLs execute in the following order:

FROM
WHERE
GROUP
SELECT

Subqueries execute in that order before main queries execute in that order. Main queries cannot access anything inside a sub query unless the sub query emits it (your sub queries emit lists of values used by IN)

So, you can't use COUNT in your WHERE, but let's look at what you're trying to do:

where 
  count(cust_id) >= 1 AND

"Where the count of cust_id is at least one.."

It's highly likely this is redundant; the way to get count to return 0 is not have any data for that cust_id, but because you're grouping and counting just one table it's you don't get a 0 count out of it - in order to show up in a result set a row has to be present, which means the count is always at least 1. Other than having null in the cust_id there is no way to make this query return 0 for any row:

SELECT cust_id, count(cust_id)
FROM t
GROUP BY cust_id

And if you're looking to eliminate nulls, you'd just say WHERE cust_id IS NOT NULL. If Orders has a not hull constraint on cust_id (is it logical to have an order that has no customer?) then there wouldn't be any need to specify it

Further, because you're then using the results in an IN, even if a NULL was selected, it gets discarded by the IN anyway- nothing is ever equal to a NULL, even another NULL so saying

WHERE x IN (1,2,3,NULL)

just gives you rows with x that is 1, 2 or 3; you don't get any rows with c as NULL. IN also doesn't care about duplicated values so this is the same as above:

WHERE x IN (1,1,2,2,2,3,NULL)

All in there is entirely no need for the clause you've put, and it can be removed. I suppose the question you're answering is "get the names of all customers from California who have ordered at least one book about computers". The at least one is a red herring; there won't be an order for them if they haven't so you can ignore it:

select cust_name 
from Customers 
where 
 state = 'California' AND 
 cust_id in(
  select cust_id 
  from Orders 
  where 
   book_id in(select book_id from Books where category = 'Computers') 
)

If however the assignment is "at least two books" then you will need to exclude the single orders. That is done with HAVING which is a where clause that runs after a GROUP BY...

Select cust_name 
from Customers 
where 
 state = 'California' AND 
 cust_id in(
  select cust_id 
  from Orders 
  where 
   book_id in(select book_id from Books where category = 'Computers') 
 group by cust_id
 having count(cust_id) > 1 AND

)

Note the use of > rather than >=

Personally, rather than nesting IN I would use JOINs and keep it all on the same level:

SELECT cust_name 
FROM 
  Customers c
  INNER JOIN Orders o on c.cust_id = o.cust_id 
  INNER JOIN Books b on o.book_id = b.book_id
WHERE 
 c.state = 'California' AND 
 b.category = 'Computers' 
GROUP BY c.cust_id, c.cust_name
HAVING COUNT(*) > 1

If you're going to use this latter form for "at least one book", remove the HAVING but keep the GROUP BY rather than using DISTINCT, as it will prevent different customers with the same name coalescing into one

CodePudding user response：

Seems no need use group by. Try the SQL statement:

Select cust_name from Customers 
where state = 'California' 
AND cust_id in
(select cust_id from Orders 
    where count(cust_id) >= 1 
        AND book_id in
        (select book_id from Books where category = 'Computers') 
)

At least you can use distinct to avoid using group by. But distinct seems no need to use in the select subquery.