In the article, we will learn how to avoid error message 130 in SQL Server. Moreover, this article will provide examples of how to perform aggregate functions on aggregate and subquery expressions.
SQL Server throws an error 130 showing us a message as “cannot perform an aggregate function on an expression containing an aggregate or a subquery” when we perform aggregate functions in subqueries or other expressions that already use aggregate functions. Let’s look at the query below to give an example of how an error message 130 appears.
CREATE TABLE TB1 (value INT); GO SELECT MAX(AVG(value)) AS maxValue FROM TB1; GO
The purpose of the query above is to create a TB1 table with a `value` column and then retrieve a maximum value from the average values of the `value` column. The result will give a message ‘Cannot perform an aggregate function on an expression containing an aggregate or a subquery’ which looks like this:
Figure 1. An error message 130
The message above appears because the MAX aggregate function cannot be performed on the AVG aggregate function that has been used as well.
The aggregate functions accept a collection of values and returns a single value as a result. It can also be used to do a summary, standard statistical functions that are imposed on tables, queries, or subqueries. The ISO standard defines five types of aggregate functions as follows.
|COUNT||Returns the number (numbers or views) of values in a column|
|SUM||Returns the sum (a total or amount) of values in a column|
|AVG||Returns the average value in a column|
|MIN||Returns the smallest or lowest (minimum) value in a column|
|MAX||Returns the largest or highest (maximum) value in the column|
Table 1. The types of aggregate functions
Here’s a table named student in the school database for our demonstration purpose. The table has columns consisting of student IDs and their scores. See the following query that is used to create them:
CREATE DATABASE school; GO USE school; GO CREATE TABLE student (student_id INT, score INT); GO INSERT INTO student VALUES (1, 8); INSERT INTO student VALUES (1, 4); INSERT INTO student VALUES (2, 7); INSERT INTO student VALUES (3, 9); INSERT INTO student VALUES (3, 7);
We can see the data in the student table by executing the ‘select * from student’ command and the result will appear as follows:
Table 2. An example of the student table
Example #1 Using the AVG function
The AVG function is used to find the average value in a column of a table or expression. Let’s see the query below to retrieve the average value in the score column from the student table.
SELECT AVG(score) AS avg_score FROM student;
The following result will appear.
Table 3. Execution of the AVG aggregate function
Example #2 Using the COUNT function with GROUP BY
The COUNT function is used to get the number of records (rows) of a column from a table. Here is a query with the COUNT function to get the number score for each student using the GROUP BY statement:
SELECT student_id, COUNT(score) AS num_rows FROM student GROUP BY student_id;
The result will appear:
Table 4. Execution of the COUNT aggregate functions
Example #3 Using a subquery with GROUP BY
We want to get the highest value of the average score in the student table that performs the MAX and AVG aggregate functions with a subquery expression. The query is as follows:
SELECT MAX(A.avg_score) AS max_avg_score FROM ( SELECT student_id, AVG(score) AS avg_score FROM student GROUP BY student_id ) AS A;
The above query ran successfully and gave the following results.
Table 5. Execution of the MAX and AVG aggregate functions
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.