Fix: Aggregate Function Error When Running Query

Writing SQL scripts may be fun until you run into bugs and errors in your code. A few of our readers have complained of the error message your query does not include the specified expression as part of an aggregate function.

The aggregate function may be tricky, but we will resolve it in this guide. However, you may check out our other guide if your SQL Server query execution failed for a dataset.

Why do I get the error your query does not include the specified expression as part of an aggregate function?

Any of the following may be the reason you get the error:

• You are using an expression that is not an aggregate function.
• The database engine can’t group the rows because it is not in the GROUP BY clause.
• You put the expression in the GROUP BY clause, but your column is not under the SELECT clause.

You should consult our other guide if you have difficulty login into Microsoft SQL Server.

How do I fix your query does not include the specified expression as part of an aggregate function?

Use the GROUP BY clause and aggregate function

1. Define the SELECT statement using the script below. 
   SELECT
country,
FROM
customers
   
2. Add the aggregate function using the script below (you must adapt it to your specific need).
   COUNT(customer_id) AS number_of_customers
   
3. Add the GROUP BY clause. In the script below, we want to count the number of customers in each country in a table. By using GROUP BY, the database will count and return the correct figure.
   GROUP BY
country;
4. Run the script and verify it fixes your query does not include the specified expression as part of an aggregate function.

NOTE

By grouping the rows before applying the aggregate functions, the GROUP BY clause enables the database engine to understand how to combine the entries and deliver the right results.

What are the 5 aggregate functions in SQL?

In SQL, you may be able to use any of the following aggregate functions:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

However, when using any of the above aggregate functions, remember the following extra considerations:

• You can only use aggregate functions on columns with numeric data.
• On columns that are part of a GROUP BY clause, aggregate functions will not work.
• A column that is also part of the SELECT list will appear in the results as a single value if you apply an aggregate function on it.

There you have it. You should now understand why you got the error, and by adapting our solutions, you should have success.

You may also be interested in our other guides, like repairing a corrupted database in SQL serverlet.

Let us know if you found this guide helpful in the comment section below. Also, if you have other ways of fixing the error, we would love to hear from you.