CODEWITHSUNDEEP
sql-server
getbruce
getbruce

Reputation: 103

sql 2008 query. I am trying to correct this error message

What is wrong with this code? What would be the correct syntax?

SELECT EmployeeID, SUM('More than 2 orders')
FROM Orders
WHERE EmployeeID >2 
GROUP BY EmployeeID


Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for sum operator.

Upvotes: 0

Views: 362

Answers (4)

Neil Moss
Neil Moss

Reputation: 6818

If I translate your comment correctly, you want the Employee ID of each employee that has 2 or more orders. The statement below gives you that:

select EmployeeID from Orders group by EmployeeID having count(EmployeeID) > 1

Upvotes: 0

Alex Martelli
Alex Martelli

Reputation: 881555

SUM('More than 2 orders') is the part that doesn't make sense, as the error message is trying to tell you -- you SUM a number field (or expression), not a varchar (i.e. string) constant.

What exactly are you trying to do here, with that "sum of a string"?

Edit: the OP clarifies in a comment (OP, editing your question is a better idea!):

I wanted to know how many employees have handled at least 2 orders and tos how the number of employees.

To show which employees "have handled at least two orders", assuming for example there's a record in the table for each order that has been handled:

SELECT EmployeeID
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(*) >= 2

this doesn't show exactly which orders those employees handled (the above clarification does not request it) nor does it show the number of such employees -- if that's what is required instead of the employees' IDs,

SELECT COUNT(DISTINCT EmployeeID)
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(*) >= 2

If neither of these guesses (or my guess at the Orders table) is exactly what you want, please show that table's schema (ideally just the parts that are relevant to the question) and a simple example of what output you want for a short sample content of the table -- and please realize that reading your mind (when you don't give the details of your problem) is not a worthwhile exercise on our part, so supplying all the relevant information, including your specs and ideally a small example, is by far the best way to get your questions answered rapidly and informatively.

Upvotes: 1

anishMarokey
anishMarokey

Reputation: 11397

It should be like:

SELECT EmployeeID, SUM(Column) FROM Orders WHERE EmployeeID >2 GROUP BY EmployeeID.

SUM cannot be applied to a varchar contains text. It can only applied to a numeric values.

Hope if you tried with group by its shown an error, aggregate function should be there so you added it, try with some column has numeric values.

Upvotes: 0

Itay Karo
Itay Karo

Reputation: 18286

should be

SELECT 
  EmployeeID,
  SUM(Col1),
  SUM(Col2),
  ..
  ..
FROM
  Orders
WHERE
  EmployeeID > 2
GROUP BY
  EmployeeID

Upvotes: 0

Related Questions