Msg 157 - An aggregate may not appear in the set list of an UPDATE statement.,sql,server

If you are trying to set one of your columns using an UPDATE statement which uses an aggregate function like SUM,MAX or MIN etc the following error will come.

Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE
statement.

To know this you jist try the following example. Suppose you have two tables called AccountBalance and AccountTransaction respectively. And AccountBalance table have the fields AccountNumber,AccountBalance,LastTransactionDate. And AccountTransaction table have the fields called AccountNumber,TransactionAmount,TransactionDate


The first table contains all the accounts in your company with the current account balance and the last transaction date. The second table contains all the transactions performed on those accounts. You are trying to update the Account Balance and Last Transaction Date columns of your Account Balance table using the following UPDATE statement:

UPDATE A
SET [AccountBalance] = SUM([TransactionAmount]),
[Last TransactionDate] = MAX([TransactionDate])
FROM [dbo].[AccountBalance] A INNER JOIN [dbo].[AccountTransaction] B
ON A.[AccountNumber] = B.[AccountNumber]

To update a table with the aggregate values of another table you have to use a sub-query as can be seen from the following UPDATE statement:

UPDATE A
SET [Account Balance] = B.[Account Balance],
[Last Transaction Date] = B.[Last Transaction Date]
FROM [dbo].[Account Balance] A INNER JOIN
(SELECT [Account Number], SUM([Transaction Amount]) AS [Account Balance],
MAX([Transaction Date]) AS [Last Transaction Date]
FROM [dbo].[Account Transaction]
GROUP BY [Account Number]) B
ON A.[Account Number] = B.[Account Number]

Invalid operator for data type. Operator equals add, type equals text.,sql,asp.net jobs,india jobs,

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type.
Operator equals add, type equals text.

I got the above error when i tried to concatenate two columns of Text datatype. I can explain this with the help of an example

I have a table called StudentComments with fields StudentID,CommentsOnTeacher,
CommentsOnSubject. If we are running the following se;ect statement will get error

SELECT [StudentID],
[CommentsOnTeacher] + [CommentsOnSubject] AS [AllComments]
FROM [dbo].[StudentComments]

To work around this error you need to CAST the TEXT column into VARCHAR first before concatenating the columns. The following query will avoid this error:

SELECT [StudentID],
CAST(CAST([CommentsOnTeacher] AS VARCHAR(8000)) +
CAST([CommentsOnSubject] AS VARCHAR(8000)) AS TEXT)
AS [AllComments]
FROM [dbo].[StudentComments]

The only drawback with this work around is that you are limiting both TEXT columns to 8000 characters each. One other way to work around this is to do the concatenation in the client application instead of having SQL Server do the concatenation.

Msg 141 - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.,sql

If you are assigning column values from a Select to some local values but not all columns are assigned to a corresponding local variable the following error message will come.

Server: Msg 141, Level 15, State 1, Line 2
A SELECT statement that assigns a value to a variable
must not be combined with data-retrieval operations.

This can be made clear with the help of following example. For example if you are running following SELECT statement in the northwend database will generate error.

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

The error is caused by the [ContactTitle] because it is not assigned to a local variable.

To avoid this error you declare a local variable called contact title and assign to the contacttitle column

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)
DECLARE @ContactTitle NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
@ContactTitle = [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

Alternatively, if you will not be using the column, simply remove it from the SELECT statement.

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

If you really need to do both, meaning to assign the value to local variables and to return the columns as a result set, you have to do it in 2 steps instead of combining them into one SELECT statement:

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

SELECT [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

SQL Server Error Message - Msg 130 - Cannot perform an aggregate function on an expression,sql,query,aggregate,function,subquery

When running select query the following error may come..

Server: Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression
containing an aggregate or a subquery.

The reason of this error is included in the error message itself. This error is comming when you are performing an aggregate function such as MAX,MIN,SUM etc on a subquery. Or on another expression which is using an aggregate function.

To illustate the error we can use an example. Suppose we have a table called Testscores with testid,studentid,score. From this table, you want to determine which exams or tests have the lowest and highest deviation in terms of the scores of the students to determine the tests which are too easy or too hard for the students.To determine the score deviation, you used the difference between the lowest score received and the highest score received for each test. You used the following SELECT statement for this purpose:

SELECT [TestID],
MAX(MAX([Score]) - MIN([Score])) AS [HighDeviation],
MIN(MAX([Score]) - MIN([Score])) AS [LowDeviation]
FROM [dbo].[TestScores]
GROUP BY [TestID]

Then the error will come as above. And the solutions are also very simple

If you simply want the test with the highest deviation in the scores, you can simply sort the output by the difference between the highest score and lowest score in descending order, as follows:

SELECT [TestID], MAX([Score]) - MIN([Score])
FROM [dbo].[TestScores]
GROUP BY [TestID]
ORDER BY MAX([Score]) - MIN([Score]) DESC

Or you can use the relative position of the column in the SELECT list to sort in the ORDER BY clause:

SELECT [TestID], MAX([Score]) - MIN([Score])
FROM [dbo].[TestScores]
GROUP BY [TestID]
ORDER BY 2 DESC

Similarly, if you simply want the test with the lowest deviation in the scores, you can simply sort the output by the difference between the highest score and lowest score in ascending order, as follows:

SELECT [TestID], MAX([Score]) - MIN([Score])
FROM [dbo].[TestScores]
GROUP BY [TestID]
ORDER BY MAX([Score]) - MIN([Score]) ASC

Now, if you want to return the highest and lowest deviation for each exam in a single result set, you have to use a sub-query or a derived table for this purpose:

SELECT [TestID], MAX([Deviation]) AS [HighestDeviation],
MIN([Deviation]) AS [LowestDeviation]
FROM (SELECT [TestID], MAX([Score]) - MIN([Score]) AS [Deviation]
FROM [dbo].[TestScores]
GROUP BY [TestID]) A
GROUP BY [TestID]
ORDER BY [TestID]






BLESSED MARY PRAY FOR US


.........................

SQL Server Error Messages - Msg 107 - The column prefix does not match with a table name or alias name used in the query.prefix,table name,query,from

When running some Select statements theire is chance to come the following the error.

Server: Msg 107, Level 16, State 3, Line 1
The column prefix does not match with a table name or
alias name used in the query.

The reason for this error is using table name as prefix of a column name and that table name is not included in the from clause. Anothe reason is you may be assigning an alias to the table name and prefix is still the table name.

I can explain the error with the help of following example

SELECT [Employees].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers]

In the above cause i have used prefixed as customers and employeed bt in from clause theire is only customers.

To avoid this error, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.

SELECT [Customers].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers]

To illustrate the second cause of this error, the following SELECT statement will generate the error:

SELECT [Customers].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers] Cust

Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:

SELECT [Cust].[FirstName], [Cust].[LastName]
FROM [dbo].[Customers] Cust