How to get average in sqlserver select query, column type is nvarchar.

Question

1.00/5 (2 votes)

See more:

Hi,
I have a problem with select query.

SQL

How to get average in sqlserver select query, column type is nvarchar.

In my reviews table rating column is nvarchar. but I inserted integer values. Now I want average of total rating.
SELECT AVG(Rating) AS rating FROM CompanyReviews;

Please give me the soluation.

Thank you.

Posted 20-May-14 21:03pm

NagaRaju Pesarlanka

Add a Solution

Comments

Maciej Los 21-May-14 3:12am

Bad table design! Bad practice!

6 solutions

Solution 5

Please read my comment to the question.

If you want to store numeric data, use numeric field instead of nvarchar. I'd strongly recommend to redesign your table.

To convert data between varchar and numeric data types, use CAST or CONVERT function[^].

Posted 20-May-14 21:29pm

Maciej Los

Solution 2

SELECT AVG(CAST(Rating AS INT)) FROM CompanyReviews

Posted 20-May-14 21:11pm

soumyajayaraj

Comments

NagaRaju Pesarlanka 21-May-14 3:19am

Thank you.
I got answere.
I am using like this query
SELECT AVG(CAST(Rating as decimal(10,2))) AS rating FROM CompanyReviews
3.202127
but I want 2 digits only after decimal.

Solution 3

You can get the average by doing cast to decimal type

SELECT AVG(CAST(Rating AS DECIMAL)) AS rating FROM CompanyReviews

Regards,
AK

Posted 20-May-14 21:18pm

Arun Kumar Ponnusamy

Comments

NagaRaju Pesarlanka 21-May-14 3:19am

Thank you.
I got answere.
I am using like this query
SELECT AVG(CAST(Rating as decimal(10,2))) AS rating FROM CompanyReviews
3.202127
but I want 2 digits only after decimal.

Solution 4

try this.. :)

SQL

SELECT AVG(convert(int,Rating)) AS rating FROM CompanyReviews;

or

SQL

SELECT AVG(cast(Rating as int)) AS rating FROM CompanyReviews;

Posted 20-May-14 21:19pm

Nirav Prabtani

Comments

NagaRaju Pesarlanka 21-May-14 3:20am

Thank you.
I got answere.
I am using like this query
SELECT AVG(CAST(Rating as decimal(10,2))) AS rating FROM CompanyReviews
3.202127
but I want 2 digits only after decimal.

Solution 6

SELECT AVG(Cast(Rating As Decimal(18,6))) AS rating FROM CompanyReviews

Posted 22-May-14 3:39am

Phanindra Baddula

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jinu _George · Accepted Answer · 2014-05-20T21:10:00

Solution 1

AVG(Cast(Rating as INT)) as avg_Rating

Posted 20-May-14 21:10pm

Jinu _George

Comments

NagaRaju Pesarlanka 21-May-14 3:12am

Thank you.
I got answere.
I am using like this query
SELECT AVG(CAST(Rating as decimal(10,2))) AS rating FROM CompanyReviews
3.202127
but I want 2 digits only after decimal.

King Fisher 21-May-14 3:22am

again cast it.
SELECT cast((AVG(CAST(Rating as float))) as decimal(10,2)) AS rating FROM CompanyReviews

better you could change datatype .

NagaRaju Pesarlanka 21-May-14 3:25am

yes, I got the answere from your query.
Thank you