Using an aggregate directly in a WHERE clause, e.g. `WHERE COUNT(*) > 10` instead of `HAVING COUNT(*) > 10`. This is the most common trigger.. Nesting an aggregate inside a WHERE condition of a correlated subquery that references the outer query's grouped columns, causing MySQL to evaluate the aggregate at the wrong scope.. A dbt model with a `WHERE` filter that references a column alias defined by an aggregate expression — dbt compiles this verbatim and MySQL rejects it at runtime.. An ADF Copy Activity or Lookup Activity using a custom SQL query with GROUP BY where the filter logic was written as WHERE instead of HAVING, often because the query was drafted in a GUI that doesn't distinguish the two.. Stored procedures or views that were migrated from SQL Server or PostgreSQL where the query optimizer was more lenient — MySQL's parser is strict about aggregate placement and rejects queries that other engines might rewrite internally.. Using an aggregate in a CHECK constraint or generated column expression, which MySQL evaluates per-row and therefore cannot resolve an aggregate function.

Step 1: Identify the exact query. In ADF, open the failed activity run → Output tab → copy the `effectiveIntegrationRuntime` error message. In dbt, run `dbt compile --select model_name` and inspect `target/compiled/` for the full SQL.. Step 2: Move aggregate conditions from WHERE to HAVING: `SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department HAVING COUNT(*) > 10;`. Step 3: If you need to filter on an aggregated result without HAVING, wrap the grouped query in a derived table: `SELECT * FROM (SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department) sub WHERE sub.cnt > 10;`. Step 4: For correlated subqueries, rewrite as a JOIN with a pre-aggregated CTE: `WITH counts AS (SELECT dept_id, COUNT(*) AS cnt FROM orders GROUP BY dept_id) SELECT e.* FROM employees e JOIN counts c ON e.dept_id = c.dept_id WHERE c.cnt > 5;`. Step 5: In dbt, replace any `WHERE` referencing an aggregate with a HAVING block, or split the model into two: one that aggregates, one that filters. Run `dbt run --select model_name` to validate the fix.. Step 6: Test the corrected query directly on the MySQL instance before redeploying: `mysql -u user -p -e "your_fixed_query" database_name`. Step 7: Re-run the failed ADF pipeline activity or trigger a dbt run. Confirm the activity status flips to Succeeded and downstream tables receive fresh data.

Medium severityquery

MySQL Error:
1111

What does this error mean?

MySQL throws error 1111 when an aggregate function like COUNT(), SUM(), AVG(), MAX(), or MIN() appears in a WHERE clause. The SQL standard requires that row-level filtering (WHERE) happens before grouping, so the database engine cannot evaluate an aggregate at that stage. The fix is almost always moving the condition to a HAVING clause, which runs after GROUP BY. In data pipelines this error surfaces in ADF Lookup or Script activities, dbt model compilation, or any ETL tool that pushes custom SQL to a MySQL source. The symptom is an immediate query failure — no partial results, no retries — and the activity logs show `ERROR 1111 (HY000): Invalid use of group function`. Any downstream table depending on that query stays stale until the SQL is corrected.

Common causes

1Using an aggregate directly in a WHERE clause, e.g. `WHERE COUNT(*) > 10` instead of `HAVING COUNT(*) > 10`. This is the most common trigger.
2Nesting an aggregate inside a WHERE condition of a correlated subquery that references the outer query's grouped columns, causing MySQL to evaluate the aggregate at the wrong scope.
3A dbt model with a `WHERE` filter that references a column alias defined by an aggregate expression — dbt compiles this verbatim and MySQL rejects it at runtime.
4An ADF Copy Activity or Lookup Activity using a custom SQL query with GROUP BY where the filter logic was written as WHERE instead of HAVING, often because the query was drafted in a GUI that doesn't distinguish the two.
5Stored procedures or views that were migrated from SQL Server or PostgreSQL where the query optimizer was more lenient — MySQL's parser is strict about aggregate placement and rejects queries that other engines might rewrite internally.
6Using an aggregate in a CHECK constraint or generated column expression, which MySQL evaluates per-row and therefore cannot resolve an aggregate function.

How to fix it

1Step 1: Identify the exact query. In ADF, open the failed activity run → Output tab → copy the `effectiveIntegrationRuntime` error message. In dbt, run `dbt compile --select model_name` and inspect `target/compiled/` for the full SQL.
2Step 2: Move aggregate conditions from WHERE to HAVING: `SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department HAVING COUNT(*) > 10;`
3Step 3: If you need to filter on an aggregated result without HAVING, wrap the grouped query in a derived table: `SELECT * FROM (SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department) sub WHERE sub.cnt > 10;`
4Step 4: For correlated subqueries, rewrite as a JOIN with a pre-aggregated CTE: `WITH counts AS (SELECT dept_id, COUNT(*) AS cnt FROM orders GROUP BY dept_id) SELECT e.* FROM employees e JOIN counts c ON e.dept_id = c.dept_id WHERE c.cnt > 5;`
5Step 5: In dbt, replace any `WHERE` referencing an aggregate with a HAVING block, or split the model into two: one that aggregates, one that filters. Run `dbt run --select model_name` to validate the fix.
6Step 6: Test the corrected query directly on the MySQL instance before redeploying: `mysql -u user -p -e "your_fixed_query" database_name`
7Step 7: Re-run the failed ADF pipeline activity or trigger a dbt run. Confirm the activity status flips to Succeeded and downstream tables receive fresh data.

Example log output

ERROR 1111 (HY000) at line 4: Invalid use of group function
mysql> SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department;
ERROR 1111 (HY000): Invalid use of group function

Frequently asked questions

What is the difference between WHERE and HAVING in MySQL?

WHERE filters individual rows before GROUP BY runs — it cannot reference aggregate functions. HAVING filters grouped results after aggregation — it can reference COUNT(), SUM(), and other aggregates. Use WHERE for row-level conditions (e.g. `WHERE status = 'active'`) and HAVING for group-level conditions (e.g. `HAVING COUNT(*) > 5`).

Can I use both WHERE and HAVING in the same MySQL query?

Yes. WHERE narrows the rows first, then GROUP BY groups them, then HAVING filters the groups. Example: `SELECT dept, COUNT(*) FROM employees WHERE hire_date > '2024-01-01' GROUP BY dept HAVING COUNT(*) > 3;` — this filters to recent hires, groups by department, then keeps only departments with more than 3 qualifying employees.

Why does MySQL error 1111 keep recurring after I fix the query?

Check for cached or compiled versions of the old query. In dbt, run `dbt clean && dbt compile` to regenerate compiled SQL. In ADF, verify the pipeline is redeployed — editing in the portal without publishing leaves the old version active. Also check stored procedures and views that may reference the original broken SQL.

Can MetricSign detect when an aggregate function error breaks a pipeline?

Yes. MetricSign captures ADF pipeline failures and extracts the MySQL error code from the activity output. Error 1111 appears in the incident detail with the failing query context, and you can set up alert rules to notify on specific error codes so the team is alerted before downstream consumers notice stale data.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html