date, sum(cast(quantity as int)) as quantity from table1

quote:

---

I have a column(quantity) with data type varchar(50) but values are like - 0, 1, 2, 3, 4

---

SELECT quantity from YourTable where quantity = 'quantity';

SELECT * FROM YourTable WHERE ISNUMERIC(quantity) = 0

select *
from   yourtable
where  isnumeric(quantity) <> 1

quote:

---

Originally posted by sunitabeck

Do you actually have a row that has the value "quantity" in the table? You can find using:

SELECT quantity from YourTable where quantity = 'quantity';

If you do remove that row. If you inserted the data from a text file and if it had a column header which was not identified as such during the import process, this can happen.

Another thing you can do is to run this query:

SELECT * FROM YourTable WHERE ISNUMERIC(quantity) = 0

That will tell you if there are any rows that are not convertible to one of the numeric types. But, that test is not perfect - ISNUMERIC will return 1 if the value is can be converted to ANY numeric type, even if it cannot be converted to INT.

---