Output count percentage in 2 decimal places

Question

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.

Answer 1

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

Answer 2

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