MySQL: Invalid Use of Group Function

MySQL: Invalid use of group function

You need to use HAVING, not WHERE.

The difference is: the WHERE clause filters which rows MySQL selects. Then MySQL groups the rows together and aggregates the numbers for your COUNT function.

HAVING is like WHERE, only it happens after the COUNT value has been computed, so it'll work as you expect. Rewrite your subquery as:

(                  -- where that pid is in the set:
SELECT c2.pid                  -- of pids
FROM Catalog AS c2             -- from catalog
WHERE c2.pid = c1.pid
HAVING COUNT(c2.sid) >= 2)

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 Code 1111. Invalid use of group function in MySQL

The cause of your error is likely that you're implying a store-level grouping without explicitly grouping on that column with a GROUP BY clause. Therefore, you're attempting to extract aggregate results that are impossible at the table-level.

You can probably resolve this by adding GROUP BY store.storeID in each of your subqueries. However, there's a lot more wrong with this query that makes it unfavorable to attempt to diagnose and resolve it.

This is all doable in a single query / grouping. Here's what your query should look like:

SELECT
    store.storeID,
    MAX(store.storeName) AS storeName,
    COUNT(DISTINCT purchase.username) AS total_purchased_user,
    MAX(player_count.players) - COUNT(DISTINCT purchase.username) AS non_purchased_user,
    SUM(purchase.cost) AS total_spent,
    AVG(purchase.cost) AS avg_spent,
    SUBSTRING(MIN(CONCAT(LPAD(purchase.cost, 11, '0'), badge.badgeName)), 12) AS cheapest_badge,
    SUBSTRING(MAX(CONCAT(LPAD(purchase.cost, 11, '0'), badge.badgeName)), 12) AS expensive_badge
FROM store
LEFT JOIN purchase ON store.storeID = purchase.storeID
LEFT JOIN badge ON purchase.badgeID = badge.badgeId
CROSS JOIN (SELECT COUNT(*) AS players FROM player) AS player_count
GROUP BY store.storeID;

What's happening here (working bottom-up):

1. GROUP BY store to ensure the results are aggregated by that, and all other metrics are calculated
2. FROM store / LEFT JOIN all other tables ensures we get metrics from every store, whether or not there are purchases for it
3. CROSS JOIN (SELECT COUNT(*) FROM players) this is a hack to give us a running total of all players that we can reference against store player-purchase counts to get the "didn't purchase" count simply and quickly, without any additional joins
4. COUNT(DISTINCT purchase.username) ensures that user counts are referenced from purchases. This also means we don't have to join on the players table in this main portion of the query to get purchase counts.
5. SUM / AVERAGE work like you had them
6. SUBSTRING(MIN(CONCAT... these calculations are using Scalar-Aggregate Reduction, a technique I invented to prevent the need for self-joining a query to get associated min/max values. There's more on this technique here: SQL Query to get column values that correspond with MAX value of another column?

Cheers!

Error Code: 1111. Invalid use of group function MYSQL ERROR

Use a subquery to compute the average grade of the course. For example:

select studentName 
from courseGrade 
where sectionID = 290001
  and grade < (
    select avg(grade) from courseGrade where sectionID = 290001
  )

Getting invalid use of group function in update query

You want to aggregate in a subquery, then join and set in the outer query:

update t1
inner join (
    select id, sum(case when due < 0 then t2.amount else 0 end) total_amount
    from t2
    where id = 3 and flag = 1
    group by id
) t2 on t1.id = t2.id
set t1.total_amount = t2.total_amount

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
;

MySQL Invalid use of group function when using max()

You can't use two aggregate functions this way, you can do this instead:

SELECT SUM(MaxPax) AS Total
FROM
(
    SELECT MAX(b.pax)  AS MaxPax
    FROM enquiry e
    LEFT JOIN booking b ON b.enquiry_id = e.id
    GROUP BY b.enquiry_id
) AS t;

So, the inner query, will give you the max pax for each inquiry. Then, in the outer query you can get the sum using SUM.

---

Related Topics