Error 1111 (Hy000): Invalid Use of Group Function

ERROR 1111 (HY000): Invalid use of group function

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Example using WHERE clause :

select *
from staff 
where salary > (select avg(salary) from staff)

Example using HAVING clause :

select deptid,COUNT(*) as TotalCount
from staff
group by deptid
having count(*) > 2

Having clause specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

MySQL Error: 1111 (Invalid use of group function)

Instead of using MAX alone try to use a subselect

If you don't want the mad for every order.id then you need to add a inner join

SELECT 
    orders.id, orders.customer_fk
FROM
    orders
        INNER JOIN
    order_details ON order_details.order_fk = orders.id
WHERE
    orders.payment_method IN ('AS' , 'AC')
        AND ((orders.order_status = 'SHP'
        AND order_details.item_status = 'SHP'
        AND (SELECT MAX(shipped_date) FROM order_details WHERE order_fk = orders.id) <= '2021-08-07')
        OR (orders.order_status = 'CAN'
        AND orders.order_date <= '2021-08-07 09:56:18'))
        AND orders.pii_status <> '1'
GROUP BY orders.id

To explain it somewhat further

SELECT MAX(shipped_date) FROM order_details WHERE order_fk = orders.id) <= '2021-08-07'

Return true or false for every Order.id as it checks for every row in the outer select what the maximum date is and then checks it against the date.

After selecting all rows you GROUP BY(which i still don't get as you have no aggregation function it) comes for every order.id.

Maybe you should try a DISTINCT

ERROR 1111 (HY000): Invalid use of group function MYSQL

You should to use HAVING statement like:

    SELECT GolDarah, count(GolDarah) JmlPasien
    FROM tblPasien
    WHERE monthname(TglLahir) IN ('July','August','September','October','November','December') 
    GROUP BY GolDarah
    HAVING count(GolDarah) = 2
    ORDER BY GolDarah;

also WHERE statement can be more effective like:

 WHERE month(TglLahir) > 5

MYSQL ERROR 1111 (HY000) at line 1: Invalid use of group function. How can I fix this error?

You can't have 2 grouping functions together with 1 group by.

Instead of

select max(count(challenge_id)) from Challenges group by hacker_id)

you can do

select max(cnt_challenge) from (select count(challenge_id) as cnt_challenge from Challenges group by hacker_id)) 

MySQL Error Code: #1111 - Invalid use of group function

You should use having with Aggregate functions.
Where is used to filter rows. Having is used to filter groups based on given condition.

SELECT a.* FROM `subscription` a, user b WHERE b.id=a.user_id 
 group by a.id
having count(a.user_id) > 1

Also, use ANSI Syntax for Join as follows:

SELECT a.* 
FROM `subscription` a 
inner join 
user b 
on b.id=a.user_id 
group by a.id
having count(a.user_id) > 1
;

[HY000][1111] Invalid use of group function

You are nesting aggregate function which is not allowed in MySQL.

You don't actually need the sum function for count distinct phone_nos for different conditions. Take the count (distinct outside the case and remove sum function and else clause of the case.

Try this:

select a.id_borrow_application,
    count(distinct case when call_type = 0 then c.phone_no end) CVG_CALL_OUT_COUNTS_6M,
    count(distinct case when call_type = 0
                and c.days <= 30 then c.phone_no end) CVG_CALL_OUT_COUNTS_1M,
    count(distinct case when call_type = 1 then c.phone_no end) CVG_CALL_IN_COUNTS_6M,
    count(distinct case when call_type = 1
                and c.days <= 30 then c.phone_no end) CVG_CALL_IN_COUNTS_1M
from t_snow_borrow_application_id a
join t_snow_call_mobile b
join t_snow_call_record_201612 c on (
        a.id_borrow_application = b.id_borrow_application
        and b.id = c.id_call_mobile
        )
group by a.id_borrow_application;

---

Related Topics