Error 1111 (HY000) - Invalid use of group function

Question

I'm getting a problem when trying to run this query:

Select 
       c.cname as custName, 
       count(distinct o.orderID) as No_of_orders, 
       avg(count(distinct o.orderID)) as avg_order_amt
From Customer c
Inner Join Order_ o
On o.customerID = c.customerID
Group by cname;

This is an error message: #1111 (HY000) - Invalid use of group function

I just want to select each customer, find how many orders each customer has, and average the total number of orders for each customer. I think it might have a problem with too many aggregates in query.

The average total of orders is the average total of orders for all customers, not for each one. One customer has x orders, there's no average for each of them, there's an average, period. — Sebas, Nov 17 '13 at 01:00
possible duplicate of [MySQL: Invalid use of group function](http://stackoverflow.com/questions/2330840/mysql-invalid-use-of-group-function) — PhoneixS, Nov 17 '14 at 12:05

score 1 · Accepted Answer · edited May 11 '21 at 21:52

The issue is that you need to have two separate groupings if you want to calculate the average over a count, so this expression isn't valid:

avg(count(distinct o.orderID))

Now it's hard to understand what exactly you mean, but it sounds as if you just want to use avg(o.amount) instead.

[edit] I see your addition now, so while the error is still the same, the solution will be slightly more complex. The last value you need, the avarage number of orders per customer, is not a value to calculate per customer. You'd need analytical functions to that, but that might be quite tricky in MySQL. I'd recommend to write a separate query for that, otherwise you would have very complex query which would return the same number for each row anyway.

score 0 · Answer 2 · answered Nov 17 '13 at 00:44

0

select c.cname, o.customerID, count(*), avg(order_total)
from order o join customer using(customerID)
group by 1,2

This will calculate the number of orders and average order total (substitute the real column name for order_total) for each customer.

answered Nov 17 '13 at 00:44

Jim Garrison

85,615
20
155
190

Both true, although not together. Once you change OrderID to Amount it is no longer the primary key (still no reason to use distinct, though, and it will change the outcome if you do). But I don't think this answers the question. – GolezTrol Nov 17 '13 at 00:45

score 0 · Answer 3 · answered Nov 17 '13 at 01:03

how many orders each customer has, average the total number of orders.

SELECT
    c1.cname AS custName,
    c1.No_of_orders,
    c2.avg_order_amt
FROM (
        SELECT
            c.id,
            c.cname, 
            COUNT(DISTINCT o.orderID) AS No_of_orders
        FROM 
            Customer c
                JOIN Order_ o ON o.customerID = c.customerID
        GROUP BY c.id, c.cname
    ) c1
    CROSS JOIN (SELECT AVG(No_of_orders) AS avg_order_amt FROM (
        SELECT 
            c.id,
            COUNT(DISTINCT o.orderID) AS No_of_orders
        FROM 
            Customer c
                JOIN Order_ o ON o.customerID = c.customerID
        GROUP BY c.id
    )) c2

Error 1111 (HY000) - Invalid use of group function

3 Answers3