[Solved] MySQL: Invalid use of group function – Mysql

The Solutions:

Solution 1: Using HAVING Clause

To fix the “Invalid use of group function” error in your MySQL query, you need to use the HAVING clause instead of the WHERE clause in the subquery. Here’s the corrected query:

-- Find the pids of parts supplied by at least two different suppliers.
SELECT c1.pid                      -- select the pid
FROM Catalog AS c1                 -- from the Catalog table
WHERE c1.pid IN (                  -- 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   -- check if the count of sids is at least 2
);

The key difference is that HAVING applies a condition on groups of rows, after they have been formed and summarized using aggregate functions like COUNT. In contrast, WHERE filters individual rows before grouping and aggregation.

In your original query, the WHERE clause was incorrectly used to filter rows based on the count of sids, but it was applied before the aggregation took place. This resulted in the “Invalid use of group function” error.

By using HAVING, you ensure that the condition on the count of sids is applied after the rows have been grouped by pid and the count of sid has been calculated.

Solution 2: Use left join to achieve the desired result

1. The provided query uses a subquery to identify parts that are supplied by at least two suppliers. However, this approach leads to the “Invalid use of group function” error because the COUNT aggregate function is used in the WHERE clause.
2. To rectify this issue, we can employ a left join between the Catalog table and itself using the pid column as the join condition.
3. By setting the join type to LEFT JOIN, we ensure that all rows from the left table (a) are included in the result set, even if there are no matching rows in the right table (b).
4. We then use the WHERE clause to ensure that the sid values from the left and right tables are not equal, indicating that the same part is supplied by different suppliers.
5. Finally, we apply a GROUP BY a.pid to ensure that we only get one row for each part, even if multiple suppliers supply that part.

The resulting query looks like this:

SELECT a.pid
FROM Catalog as a
LEFT JOIN Catalog as b USING( pid )
WHERE a.sid != b.sid
GROUP BY a.pid;

This query should return only rows where at least two rows exist with the same pid but there are at least 2 sids. To make sure you get back only one row per pid`, a grouping clause has been applied.

Solution 3: Breaking Down Nested Aggregate Function

The "Invalid use of group function" error typically occurs when you have aggregate functions within a WHERE clause. To resolve this issue, you can break down the nested aggregate function into two separate steps:

1. Save the result of the inner aggregate function into a temporary variable:

SELECT AVG(close) INTO @avg_close FROM prices;

2. Use the temporary variable in the outer aggregate function:

SELECT SUM(@avg_close) FROM prices;

By splitting the nested aggregate function into two steps, you eliminate the error and achieve the desired result.