Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following sql query code and I can not get to execute the desired output.

SELECT Deal_type, Price_type, 
cast(sum(Deal_type)/count(Deal_type)*1.0 as decimal(12,2)) as results
FROM [dbo].[all] 
WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo') 
GROUP BY Deal_type,Price_type order by Deal_type

I am trying to display the percentage values in decimal and currently its displaying the error message -

Operand data type nvarchar is invalid for sum operator.

Any help with the above query would be very much appreciated.

share|improve this question
    
The error message tells all. What do you expect to be, say, 'RMBS' + 'abs'? Because sum() does exactly this - that is, it does not, because it doesn't know what to do. (Reading for the second time: do you want to get the percentage of 'RMBS' rows of all ('RMBS','abs', 'cmbs', 'clo', 'cdo') rows?) –  dezso Feb 26 '13 at 10:09

2 Answers 2

up vote 1 down vote accepted

Are you looking for something like this..

SELECT 
    Deal_type, 
    Price_type, 
    count(isnull(Deal_type, 'x')) * 100 / (
        SELECT count(1) 
        FROM users 
        WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo') 
    ) as results
FROM [dbo].[all] 
WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo') 
GROUP BY Deal_type,Price_type 
ORDER BY Deal_type
share|improve this answer

It is hard to understand what you want to do without knowing the datatypes of your table columns. As I can see you have Deal_type as nvarchar. So server gives you error, that it cannot sum nvarchars. As it is said in http://msdn.microsoft.com/en-us/library/ms187810.aspx, SUM works with the following datatypes: tinyint, int, smallint, bigint, decimal, money and smallmoney, float and real

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.