Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Handle Error Message 130 in SQL Server – Querychat

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.

Aggregate Function

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.

Function Description
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

Examples

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:

student_id score
1 8
1 4
2 7
3 9
3 7

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.

avg_score
7

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:

student_id num_rows
1 2
2 1
3 2

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.

max_avg_score
8

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc