Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Aggregate function

Posted on 2011-09-20
11
Medium Priority
?
300 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:karinos57
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 36569208
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
 

Author Comment

by:karinos57
ID: 36569250
how do i change it?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36569337
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1000 total points
ID: 36569368
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36569397
*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
 

Author Comment

by:karinos57
ID: 36569474
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36569511
ROUND(..., 2) is what you would use to round an numerical value to two decimal places.
0
 

Author Comment

by:karinos57
ID: 36569539
where shoud i add the Round 2?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36569557
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36569584
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
 

Author Closing Comment

by:karinos57
ID: 36575451
tx
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question