Solved

Aggregate function

Posted on 2011-09-20
11
296 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 250 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 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 59

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 59

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 59

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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