Aggregate function

hi,
i am having an issue when testing this aggregate function.  For instance,
i have 2 columns:
Product and Price
when i wrtie sql like this to show the average price; i get this error "Operand data type nchar is invalid for avg operator"


SELECT Product, avg(Price )as total
FROM dbo.Summary
group by Product
karinos57Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
your price is not a numeric type  (even if it looks like it has numeric data in it)

either cast it to a numeric  or change the column itself
0
 
karinos57Author Commented:
how do i change it?
0
 
sdstuberCommented:
open the column list in the object explorer, right click on the price column,  select "Modify",  when the grid appears on the right,  edit the data type to be some numeric type.

close and save the changes.

you will get a warning that some data may be lost if you convert.  If you have a backup then go ahead and continue.
If you don't have a backup, then stop,  take a backup and start over.

Assuming all data in your column is numeric, then it should convert fine.

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Backing up, Sean, you need to have the table designed correctly, so the HOW is to ALTER TABLE either via T-SQL DDL with same name or throw SSMS via UI. As an alternative, you can take advantage of ISNUMERIC() and CONVERT().

SELECT Product, AVG(CONVERT(MONEY, Price))AS Total
FROM dbo.Summary
WHERE ISNUMERIC(Price) = 1
GROUP BY Product

Couple notes:
- the function on Price in WHERE may hurt performance a bit.
- you can use DECIMAL, NUMERIC, or other appropriate data type instead of MONEY; however, note MONEY will convert a value with '$' whereas DECIMAL will fail.

Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
*throw == through.
Plus, I did not see Sean's previous note when I started typing. That is the SSMS UI method. There is a setting in Options > Designers > Table and Database Designers > "Prevent saving changes that require table re-creation" that may need to be disabled to get this to work in that method, but give it a shot. Aside from disabling that setting, you can use ALTER TABLE ALTER COLUMN as stated, although, it is probably best to add a new column, convert the valid data elements and default others appropriately. You can then remove the original column ...
0
 
karinos57Author Commented:
thnx.  I used this and it works great now.  but how can i round the numbers? i get too many decimals:
use DSR
SELECT ScriptName, AVG(CAST(KPI_Score AS numeric(12,2)))KPI_Score
FROM dbo.KPI_Summary
group by ScriptName
0
 
Kevin CrossChief Technology OfficerCommented:
ROUND(..., 2) is what you would use to round an numerical value to two decimal places.
0
 
karinos57Author Commented:
where shoud i add the Round 2?
0
 
sdstuberCommented:
depends,  do you want the average of the rounded values?

AVG(round(CAST(KPI_Score AS numeric(12,2)),2))

or do you want the rounded value of the average?

round(AVG(CAST(KPI_Score AS numeric(12,2))),2)

0
 
Kevin CrossChief Technology OfficerCommented:
Yes, I am sorry. I am popping online on breaks in action of my training and so my postings have been sparse on words. :) Sean's explanation is the reason I showed on its own as you can place that where ever you want the rounding to occur.
0
 
karinos57Author Commented:
tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.